Loop to remove duplicates only works for the first iteration

user_2670777 0
Viewed 47

I have the following simple loop to remove duplicates from individual columns. It, however, only works for the first iteration i = 1. The second iteration i = 2 gave

Application_defined or object_defined error.

The code is basically copied from Microsoft's web site. Thus, I'm not sure why it doesn't work.

Sub remove_dup()    
    Dim i As Integer
    For i = 1 To 33
        Range(ActiveSheet.Cells(1, i), ActiveSheet.Cells(100, i)).RemoveDuplicates Columns:=i, Header:=xlNo
    Next i
End Sub
vba excel loops
1 Answers

In columns parameter just use 1 instead of i as every iteration is removing duplicates from single column.

Sub remove_dup()
Dim i As Integer
    For i = 1 To 33
        Range(ActiveSheet.Cells(1, i), ActiveSheet.Cells(100, i)).RemoveDuplicates Columns:=1, Header:=xlNo
    Next i
End Sub
user_55147470
Related