用VBA删除Excel工作表中的重复行

用VBA删除Excel工作表中的重复行：如果要在Excel中用VBA的方法以根据某列内容删除重复的行，即当某列有重复数据时仅保留一行，可以用下面的VBA代码。

假如以A列为参考，工作表的第一行为标题行，数据从第二行开始。

方法一：用工作表函数CountIf判断该行是否重复

Sub 删除重复行1()Dim i As LongApplication.ScreenUpdating = FalseFor 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 IfNextApplication.ScreenUpdating = TrueEnd Sub

方法二：先高级筛选，再删除隐藏行

Sub 删除重复行2()Dim rCell As Range, rRng As Range, dRng As RangeOn Error Resume NextApplication.ScreenUpdating = FalseSet rRng = Range(“A1:A” & Range(“A65536”).End(xlUp).Row)rRng.AdvancedFilter Action:=xlFilterInPlace, unique:=TrueFor 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 IfNextIf Not dRng Is Nothing Then dRng.deleteActiveSheet.ShowAllDataApplication.ScreenUpdating = TrueEnd Sub

接受自己的失败面，是一种成熟，更是一种睿智;