VBA RemoveDuplicates: Cannot set Columns by variable

Question

I worked with RemoveDuplicates and cannot find any other way than to hard code the columns settings. This is code would work:

Worksheets("SheetName").Activate
ActiveSheet.Range("A:Z").RemoveDuplicates _
Columns:=VBA.Array(1, 2, 3), _
    Header:=xlYes

But this code would throw "runtime error 5":

Dim ArrayColumns() As Variant
ArrayColumns = VBA.Array(1, 2, 3)

Worksheets("SheetName").Activate
ActiveSheet.Range("A:Z").RemoveDuplicates _
Columns:=ArrayColumns, _
    Header:=xlYes

As I actually would like to use an array with 100+ entries that might even change over time, I am not so happy with the first solution.

Can you help me?

Cheers, Peter

Shai Rado · Accepted Answer · 2016-10-06 09:07:50Z

1

Add ( before and ) after your ArrayColumns

Sub RemDuplicates()

Dim ArrayColumns() As Variant
Dim Sht As Worksheet

Set Sht = ThisWorkbook.Sheets("SheetName")
ArrayColumns = Array(1, 2, 3)

With Sht
    .Range("A:Z").RemoveDuplicates Columns:=(ArrayColumns), Header:=xlYes
End With

End Sub

answered Oct 6, 2016 at 9:07

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

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

3 Comments

Peter Frey Over a year ago

Thanks, this works now. I thought I have tried this solution but that time it must have been a Long-array or any other problem. PS: is With Sht .Range.... End With faster than my Sht.Range....? Or why did you use this?

Shai Rado Over a year ago

@PeterFrey maybe it's my style of coding, but i prefer using With statements, in case in the future you want to run more coding on this worksheet, then it's easier to set reference.

Jbjstam Over a year ago

Why exactly does this work, @Shai Rado ? Do the brackets force evaluation, or something?

NeilS · Accepted Answer · 2017-08-17 05:29:05Z

1

Just a heads up with the original posting where it was stated ArrayColumns = VBA.Array(1, 2, 3). One would only use VBA.Array with RemoveDuplicates if one had Option Base 1 set for the module. That is why ArrayColumns = Array(1, 2, 3) worked finally [assuming Option Base was default 0]

answered Aug 17, 2017 at 5:29

NeilS

211 bronze badge

Collectives™ on Stack Overflow

VBA RemoveDuplicates: Cannot set Columns by variable

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related