Coding Array in Excel VBA

Question

I have the following code to remove duplicates in Excel:

    AlertRange.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _
    7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, _
    34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49), Header:=xlNo

It works fine. But could there be a better way to provide the array to the "Columns" parameter? The way it codes looks stupid.

Array(1:49)? or Array("1:49") Unsure if that works the same as Range() or Columns() — Chrismas007
– Chrismas007, Commented Jan 28, 2015 at 14:48

Gary's Student · Accepted Answer · 2015-01-28 16:52:07Z

3

Since RemoveDuplicates wants a Variant, zero-based, array rather than a Range you could make it with a loop:

Sub Macro()
    Dim ary(0 To 48)

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

    Range("$A$1:$BB$20").Select
    ActiveSheet.Range("$A$1:$BB$20").RemoveDuplicates Columns:=(ary), Header _
        :=xlNo
End Sub

Please note the (ary)
This is to accommodate a very old VBA bug.

edited Jan 28, 2015 at 16:52

answered Jan 28, 2015 at 15:11

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

2 Comments

gembird Over a year ago

+1 good point with the (ary). For more info this might be usefull link.

chapter3 Over a year ago

@Gary'sStudent... thx! i make it become a small routine to generate the array.

Collectives™ on Stack Overflow

Coding Array in Excel VBA

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related