How to use range.RemoveDuplicates in Excel VBA?

*(Learned later that I should use \[Excel\] prefix in the title. Cannot edit title. But the information is there.)*

In Excel VBA, I want to select a range (n rows, m columns) and remove duplicates.

The following works for 7 columns:

r.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7), Header:=xlYes

But I want it work with a variable number of columns.

I've tried the following. None works.

' Selection should be the upper-left corner (header row) Set r = Range(Selection, Selection.End(xlDown).End(xlToRight)) r.Select

r.RemoveDuplicates r.RemoveDuplicates Header:=xlYes

ncol = r.Columns.Count ReDim dupecol(1 To ncol) For i = 1 To ncol: dupecol(i) = i: Next r.RemoveDuplicates Columns:=dupecol, Header:=xlYes r.RemoveDuplicates Columns:=(dupecol), Header:=xlYes

The first two RemoveDuplicates simply do nothing (!).

The last code snippet results in error 5: invalid procedure call or argument in both cases.

I confirmed that r.Address, ncol, Typename(dupecol), dupecol(1) and dupecol(ncol) are what they should be.

Any idea how to make it work without using hardcoded Array(...)?

*EDIT.... For testing purposes, I used the same conditions that worked with hardcoded Array(...). So, r.Address comprises only 7 columns, multiple rows and no adjacent data; ncol is 7; Typename(dupecol) is Variant(); LBound(dupecol) is 1 (\*); UBound(dupecol) is 7; dupecol(1) is 1; and dupecol(ncol) is 7.*

(\*) UPDATE.... As u/ZetaPower noted, ***LBound*** ***must be zero*** for it work with RemoveDuplicates Columns:=(dupecol). That is, it requires ReDim dupecol(***0 to ncol-1***).