舉例說明。
有數據表A列及B列,要求將A列數據排除B列後寫入到D列。
表格初始數據如圖:
編制代碼如下:
Sub 數據排除()
Dim mys As String
Dim I, J As Integer
Dim b As Object
mys = "數據排除"
Set b = CreateObject("Scripting.Dictionary")
Sheets(mys).Select
I = 2
Do While Cells(I, 2) <> "" '將名單放入字典
m_key = Cells(I, 2)
M_ITEM = I - 1
If Not b.Exists(m_key) Then
b.Add m_key, M_ITEM
End If
I = I + 1
Loop
J = 2 '記錄C列寫入數據的最新行號
I = 2
Do While Cells(I, 1) <> "" '將A列數據排除B列後存入C列
m_key = Cells(I, 1)
If Not b.Exists(m_key) Then
Cells(J, 3) = m_key
J = J + 1
End If
I = I + 1
Loop
End Sub
運行該宏後,結果如圖: