Remove duplicate rows based on all columns via VBA-CodePudding

I found a great solution from this post: Removing duplicate rows after checking all columns

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

I tried the script, and wanted to adjust to my case: I want to delete all duplicated rows based on all columns except the first column (i.e., columns B to U). Should I use ws.Range("B2:U3000") instead of UsedRange?

CodePudding user response：

You can either use ws.Range("B2:U3000") or below code

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

The final code should look like this.

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