我的任务是从数据集中删除重复项,但是以特定的方式;我需要应用具有两个条件的过滤器,然后删除除第一个之外的所有可见行,我将在运行中编辑 .
我确信解决方案依赖于循环过滤每个条件并删除相关行 . 但是,我不知道该怎么做 . 使用偏移是不好的;设置和偏移使用和可见单元格的范围似乎不起作用;它始终偏离第1行,而不是可见行 .
范围Dive来自工作表WS,而不是“编译工作表”,其中自动过滤器和复制删除正在进行 .
Sub Dupe_killer()
Dim List As Worksheet
Dim Dive As Range
Dim Hit As Range
Set List = Sheets.Add
Dim aRow As Range
Dim fRow As Range
Dim lRow As Range
Dim r As Range
Dim Rng As Range
Dim FilterRange As Range
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
End With
Worksheets("Compilation Sheet").Activate
If ActiveSheet.FilterMode = False Then
ActiveSheet.Range("A1:bc1").AutoFilter
End If
ActiveWorkbook.Worksheets("Compilation Sheet").AutoFilter.Sort.SortFields.Clear
ActiveSheet.Range("$A$1:$BC$11188").AutoFilter Field:=2, Criteria1:=RGB(255 _
, 0, 255), Operator:=xlFilterCellColor
List.Range("A:A").Value = Worksheets("Compilation Sheet").Range("B:B").Value
List.Range("A:A").RemoveDuplicates Columns:=Array(1)
Set r = List.Range("A2")
Set Dive = Range(r, r.End(xlDown))
For Each Hit In Dive
With Worksheets("Compilation Sheet")
.Range("A1:BC1").AutoFilter Field:=2, Criteria1:=Hit
.Range("A1:BC1").AutoFilter Field:=10, Criteria1:="*", Criteria2:="*,*", Operator:=xlAnd
End With
Set FilterRange = ActiveSheet.UsedRange.Offset(2, 0) _
.SpecialCells(xlCellTypeVisible)
FilterRange.Select
Next Hit
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
End With
End Sub
2 回答
您希望将Dive中的范围设置为仅可见行 .
谷歌的语法
我想我已经破解了它 . 找到了一个用于选择第一个可见单元格的漂亮代码 . 然后我可以隐藏那一行并删除所有可见的 .