Remove duplicate rows based on all columns via VBA

Question

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?

What did YOU try. Please post the code you tried to solve this problem. — freeflow
– freeflow, Commented Oct 11, 2022 at 9:50
The above code is the one I tried. I used the solution from the previous post and adjusted a bit to my data, but get stuck how to exclude the first column — Laura
– Laura, Commented Oct 11, 2022 at 10:07
The example in the link has a double loop, rows and columns. You'll need both loops. UsedRange.Rows iterates through all rows, I think you don't want to change that. The line that needs the change is: "For Col = Rng.Columns.Count To 1 Step -1". You want to change the starting point, so "To 2 step -1" instead of "To 1 step -1" — hennep
– hennep, Commented Oct 11, 2022 at 10:14

Yogendra Chandrashekar · Accepted Answer · 2022-10-11 20:35:34Z

1

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

answered Oct 11, 2022 at 20:35

Yogendra Chandrashekar

1311 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Remove duplicate rows based on all columns via VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related