excel運用VBA刪除工作表中的重復行

如果要在Excel中用VBA的方法以根據某列內容刪除重復的行，即當某列有重復數據時僅保留一行，可以用下面的VBA代碼。假如以A列為參考，工作表的第一行為標題行，數據從第二行開始。

方法一：用工作表函數CountIf判斷該行是否重復

Sub 刪除重復行1()
Dim i As Long
Application.ScreenUpdating = False
For i = Range("A65536").End(xlUp).Row To 3 Step -1
If WorksheetFunction.CountIf(Range("A2:A" & i), Cells(i, 1)) > 1 Then
Cells(i, 1).EntireRow.delete
End If
Next
Application.ScreenUpdating = True
End Sub

方法二：先高級篩選，再刪除隱藏行

Sub 刪除重復行2()
Dim rCell As Range, rRng As Range, dRng As Range
On Error Resume Next
Application.ScreenUpdating = False
Set rRng = Range("A1:A" & Range("A65536").End(xlUp).Row)
rRng.AdvancedFilter Action:=xlFilterInPlace, unique:=True
For Each rCell In rRng
If rCell.EntireRow.Hidden = True Then
If dRng Is Nothing Then
Set dRng = rCell.EntireRow
Else
Set dRng = Application.Union(dRng, rCell.EntireRow)
End If
End If
Next
If Not dRng Is Nothing Then dRng.delete
ActiveSheet.ShowAllData
Application.ScreenUpdating = True
End Sub