VBA: Create Variabl Array Column Number for .RemoveDuplicates
I'm stumped on this and couldn't find an answer via a search.
This comes from the macro recorder when I remove duplicates from the last three columns of my range.
Sub Macro1()
' Macro1 Macro
Range("A1:E8").Select
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$E$8").RemoveDuplicates _
Columns:=Array(3, 4, 5), Header:=xlYes
End Sub
I want to make a macro that does this using variables instead of Array (3, 4, 5) but I get an error when I try to pass an array built from variables.
Sub MyTry1()
Dim iArray() As Integer, i As Integer
With ActiveSheet.Range("$A$1:$E$8")
ReDim iArray(1 To .Columns.Count - 2)
For i = 1 To 3
iArray(i) = i + 2
Next i 'Result is iArray= (3, 4, 5)
.RemoveDuplicates Columns:=iArray, Header:=xlYes
'returns Run-time error "5": Invalid procedure call or argument
End With
End Sub
I have tried Integer, Long and Variant datatypes but no luck.
0
source to share
1 answer
You can use the null Variant Array of integers and wrap the array reference in parentheses as shown below
Sub MyTry2()
Dim iArray As Variant, i As Integer
Dim rData As Range
Set rData = Range("$A$1:$E$8")
With rData
ReDim iArray(0 To .Columns.Count - 3)
For i = 0 To UBound(iArray)
iArray(i) = i + 3
Next i
.RemoveDuplicates Columns:=(iArray), Header:=xlYes
End With
End Sub
+2
source to share