CODEWITHSUNDEEP
vbaexcelduplicates
Davy Heutmekers
Davy Heutmekers

Reputation: 25

Removing Duplicates using VBA

I'm trying to remove duplicate rows in my Excel Sheet using Visual Basic. The problem is that the amount of Rows will be variable.

Sub RemoveDuplicates()
   Range("A1").Select
   ActiveSheet.Range(Selection, ActiveCell.CurrentRegion).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

The problem here is that Columns:=Array(1, 2) isn't a variable. It should always look from column 1 until the last filled column (the .CurrentRegion).

Can someone please help me!

Upvotes: 1

Views: 5351

Answers (3)

user6432984
user6432984

Reputation:

Gary's Student has the correct answer.

I'm just having a little fun: 

Dim a As Variant
With Range("A1").CurrentRegion
    a = Evaluate("Transpose(Row(1:" & .Columns.Count & "))")
    ReDim Preserve a(0 To UBound(a) - 1)
    .RemoveDuplicates Columns:=(a), Header:=xlYes
End With

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96753

One way is to create the array dynamically.

Once the block has been defined, we know exactly how many columns the block contains:

Sub luxation()
    Dim A1 As Range, rng As Range, cCount As Long
    Set A1 = Range("A1")
    Set rng = A1.CurrentRegion

    cCount = rng.Columns.Count - 1
    ReDim ary(0 To cCount)

    For i = 0 To cCount
        ary(i) = i + 1
    Next i

    rng.RemoveDuplicates Columns:=(ary), Header:=xlYes
End Sub

Note the encapsulation of ary() in the last line!

Upvotes: 3

Shai Rado
Shai Rado

Reputation: 33672

Maybe you want something like this:

Sub RemoveDuplicates()

   Dim LastCol  As Long
   Dim LastRow  As Long
   Dim ColArray As Variant
   Dim i As Long

   ' modify "Sheet1" to your sheet's name
    With Sheets("Sheet1")
        ' find last column with data in first row ("header" row)
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        ' find last row with data in column "A"
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        ReDim ColArray(0 To LastCol - 1)
        For i = 0 To UBound(ColArray)
            ColArray(i) = i + 1
        Next i

        .Range(.Cells(1, 1), .Cells(LastRow, LastCol)).RemoveDuplicates Columns:=(ColArray), Header:=xlYes    
    End With

End Sub

Upvotes: 1

Related Questions