利用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

分享给朋友:
您可能感兴趣的文章:
随机阅读: