用excel巨集刪除重複資料

這是今天做的，感覺挺實用。

=================================================
Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer
Dim sheetA_Name As String
Dim sheetB_Name As String
Dim sheetA_Range As String
Dim sheetB_Range As String

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

sheetA_Name = Sheets("rule").Cells(1, 1)
sheetA_Range = Sheets("rule").Cells(1, 2)
sheetB_Name = Sheets("rule").Cells(2, 1)
sheetB_Range = Sheets("rule").Cells(2, 2)

' Get count of records to search through (list that will be deleted).
iListCount = Sheets(sheetA_Name).Range(sheetA_Range).Rows.Count

' Loop through the "master" list.
For Each x In Sheets(sheetB_Name).Range(sheetB_Range)
' Loop through all records in the second list.
For iCtr = 1 To iListCount
' Do comparison of next record.
' To specify a different column, change 1 to the column number.
If x.Value = Sheets(sheetA_Name).Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets(sheetA_Name).Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub
=================================================

程式其實是從M$網站上co下來的，只改了資料來源設定方式
sheetA_Name,sheetA_Range分別是母資料的sheet名稱跟資料範圍
sheetB_Name,sheetB_Range分別是子資料的sheet名稱跟資料範圍
這四個值透過rule這個sheet的A1,B1,A2,B2來設定，然後巨集執行後，就會把母資料的欄位中跟子資料重複的資料刪掉。

不過執行時間比原先預期的要久，才少少三千多筆的母資料跟八百多筆的子資料，跑這個巨集就要一分多鐘的時間，真的相當出乎意料啊！

如果資料再多個兩三倍，不知道要不要搞來一台超級電腦？