如果要在Excel中用VBA的方法以根据某列内容删除重复的行,即当某列有重复数据时仅保留一行,可以用下面的VBA代码。假如以A列为参考,工作表的第一行为标题行,数据从第二行开始。
方法一:用工作表函数CountIf判断该行是否重复
Sub 删除重复行1()Dim i As LongApplication.ScreenUpdating = FalseFor i = Range("A65536").End(xlUp).Row To 3 Step -1If WorksheetFunction.CountIf(Range("A2:A" & i), Cells(i, 1)) > 1 ThenCells(i, 1).EntireRow.deleteEnd IfNextApplication.ScreenUpdating = TrueEnd Sub
WPS之家https://www.qiangguoshuo.com/wps/
方法二:先高级筛选,再删除隐藏行
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 rRngIf rCell.EntireRow.Hidden = True ThenIf dRng Is Nothing ThenSet dRng = rCell.EntireRowElseSet dRng = Application.Union(dRng, rCell.EntireRow)End IfEnd IfNextIf Not dRng Is Nothing Then dRng.deleteActiveSheet.ShowAllDataApplication.ScreenUpdating = TrueEnd Sub
转载声明:本站发布文章及版权归原作者所有,转载本站文章请注明文章来源!