VBA：为.RemoveDuplicates创建列号Variabl数组 [英] VBA: Making a Column Number Variabl Array for .RemoveDuplicates

问题描述

我被困在这一个上，无法通过搜索找到答案。

I'm stumped on this one and haven't been able to find the answer through search.

这是来自宏记录器，当我删除重复的我的范围的最后3列。

This comes from the macro-recorder when I remove duplicates from the last 3 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

我想制作一个宏这使用变量而不是Array（3，4，5）
，但是当尝试传递由变量构建的数组时，我收到错误。

I want to make a macro that does this using variables instead of Array(3, 4, 5) but I get an error when trying 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

我尝试过Integer，Long和Variant数据类型，但没有运气。

I've tried Integer, Long and Variant data types, but no luck.

推荐答案

您可以使用基于零的Variant数组的整数，并在括号中包装数组引用，如下所示

You can use a zero-based 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

这篇关于VBA：为.RemoveDuplicates创建列号Variabl数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！