通过VBA删除基于所有列的重复行

Question

我从这篇文章中找到了一个很好的解决方案：在检查所有列后删除重复的行

Sub Remove_DuplicateRows()
Dim intArray As Variant, i As Integer
Dim rng As Range
Dim ws As Worksheet

Call Open_Workbook

Set ws = Workbooks("Sales2021.xlsm").Sheets("Reporting Template")
ws.Activate
Set rng = ws.UsedRange.Rows
With rng
ReDim intArray(0 To .Columns.Count - 1)
For i = 0 To UBound(intArray)
intArray(i) = i + 1
Next i
.RemoveDuplicates Columns:=(intArray), Header:=xlYes
End With
End Sub

我尝试了这个脚本，并想根据我的情况进行调整：我想删除除了第一列(即B到U列(之外的所有列的所有重复行。我应该使用ws.Range("B2:U3000"(而不是UsedRange吗？

Answer 1

您可以使用ws.Range("B2:U3000"(或低于代码

Set rng = ws.UsedRange.Offset(0, 1).Resize(ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count - 1)

最后的代码应该是这样的。

Sub Remove_DuplicateRows()
Dim intArray As Variant, i As Integer
Dim rng As Range
Dim ws As Worksheet

Call Open_Workbook

Set ws = Workbooks("Sales2021.xlsm").Sheets("Reporting Template")
ws.Activate
Set rng = ws.UsedRange.Offset(0, 1).Resize(ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count - 1)
With rng
ReDim intArray(0 To .Columns.Count - 1)
For i = 0 To UBound(intArray)
intArray(i) = i + 1
Next i
.RemoveDuplicates Columns:=(intArray), Header:=xlYes
End With
End Sub