Excel VBA remove Duplicates using for

Question

I'm trying to RemoveDuplicates for each column EX) removeduplicates for A1:A100, columns=1 / B1:B10-, colums=2......so on

and the boldes codes don't work please help me out

Sub DeleteDulpicates()

Dim i As Integer
Dim X As Integer
Dim Endrow As Long
Dim Endcolumn As Long
Dim sht As Worksheet

Set sht = ActiveSheet

Endrow = Cells.SpecialCells(xlCellTypeLastCell).Row
Endcolumn = sht.Cells.SpecialCells(xlCellTypeLastCell).Column


For i = 1 To Endcolumn
    
***Range(sht.Cells(i, 1), sht.Cells(i, 15000)).RemoveDuplicates Columns:=i, Header:=xlNo***
Next
    
End Sub

From documentation, it looks like you need an array of column indexes so Columns:=Array(i) may fix the error. (also Endrow doesn't qualify the worksheet (i.e. sht. is missing. learn.microsoft.com/en-us/office/vba/api/… — Tragamor
– Tragamor, Commented Oct 7, 2021 at 9:05
@Tragamor: For one column a number is fine. For multiple columns, it has to be a 1. variant 2. zero-based array, and 3. it has to be evaluated if you don't want to hard-code it (e.g. Array(1, 2)). Check out this example. — VBasic2008
– VBasic2008, Commented Oct 7, 2021 at 9:45

norie · Accepted Answer · 2021-10-07 09:52:44Z

1

You have row and column the wrong way round in Cells, and if you are removing duplicates from individual columns the Columns argument should be 1.

Option Explicit

Sub DeleteDulpicates()
Dim sht As Worksheet
Dim i As Long
Dim Endcolumn As Long
Dim Endrow As Long

    Set sht = ActiveSheet

    Endrow = Cells.SpecialCells(xlCellTypeLastCell).Row
    Endcolumn = sht.Cells.SpecialCells(xlCellTypeLastCell).Column

    For i = 1 To Endcolumn
        With sht
            .Range(.Cells(1, i), .Cells(Endrow, i)).RemoveDuplicates Columns:=1, Header:=xlNo
        End With
    Next i

End Sub

edited Oct 7, 2021 at 9:52

answered Oct 7, 2021 at 9:12

norie

9,9372 gold badges14 silver badges19 bronze badges

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

1 Comment

ppark Over a year ago

thank you you saved my life.....my savior..........my angel.....i love u

Collectives™ on Stack Overflow

Excel VBA remove Duplicates using for

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related