Removing Duplicates using VBA

Question

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!

Gary's Student · Accepted Answer · 2016-12-17 14:32:52Z

3

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!

answered Dec 17, 2016 at 14:32

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

1 Comment

user6432984 Over a year ago

It's interesting how the parenthesis cause the array to be passed ByVal. I had given up on my answer till I saw that you and Shai Rado used them. Kudos

user6432984 · Accepted Answer · 2016-12-17 17:23:19Z

2

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

answered Dec 17, 2016 at 17:23

user6432984

Comments

Shai Rado · Accepted Answer · 2016-12-17 14:30:14Z

1

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

answered Dec 17, 2016 at 14:30

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Collectives™ on Stack Overflow

Removing Duplicates using VBA

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related