Excel 2013 VBA Range.RemoveDuplicates问题指定数组 [英] Excel 2013 VBA Range.RemoveDuplicates issue specifying array

问题描述

我正在扫描重复的工作表具有不同的列数量

我试图指定 Range.RemoveDuplicates 通过使用这样的字符串：

假设这张表中有5列

  Dim Rng As Range 
 Dim i As Integer 
 Dim lColumn As Integer 
 Dim strColumnArray As String 
 
 With ActiveSheet 
 
 lColumn = Cells（1，Columns.Count）.End（xlToLeft）.Column 
 
 strColumnArray =1
对于i = 2到lColumn 
 
 strColumnArray = strColumnArray& ，& 
 
 Next i 
 
'String最后为1，2，3，4，5
 
设置Rng =范围（单元格1，1），Cells（1，lColumn）.End（xlDown））
 Rng.RemoveDuplicates列：= Array（strColumnArray），标题：= xlNo 
 
结束
  

我得到运行时错误13类型不匹配错误

所以我读了帖子，看到有人通过将它指定为这样的数组：

  Dim Rng As Range 
 Dim i As Integer 
 Dim lColumn As Integer 
 Dim strColumnArray（）As String 
 
 With ActiveSheet 
 
 lColumn = Cells（1， Columns.Count）.End（xlToLeft）.Column 
 
 ReDim strColumnArray（lColumn）As String 
 
 For i = 1 To lColumn + 1'数组需要以1开头
 
 strColumnArray（i）= i 
 
 Next i 
 
设置Rng =范围（单元格（1，1），单元格（1，lColumn） .End（xlDown））
 Rng.Remo veDuplicates列：= strColumnArray，标题：= xlNo 
 
结束
  

我我试过这样：

  Rng.RemoveDuplicates（strColumnArray，Header：= xlNo）
  

amd这样：

  Rng .RemoveDuplicates（Columns：= Array（strColumnArray），Header：= xlNo）
  

在这一点上我一定只是错过了

任何帮助请注意

解决方案

我不得不回去重新阅读我的帖子上。我认为数组必须是一个 Variant 。无论如何，这似乎有效：

  Sub RemoveDupes（）
 Dim Rng As Range 
 Dim i As Integer 
 Dim lColumn As Integer 
 Dim ColumnArray As Variant 
 
 With ActiveSheet 
 lColumn = Cells（1，Columns.Count）.End（xlToLeft）.Column 
 ReDim ColumnArray（lColumn  -  1）
对于i = 0到lColumn  -  1'数组需要以1 
开头ColumnArray（i）= i + 1 
 Next i 
设置Rng =范围（单元格（1，1），单元格（1，lColumn）.End（xlDown））
 Rng.RemoveDuplicates列：=（ColumnArray），标题：= xlYes 
结束与
 End Sub 
  

The sheets that I am scanning for duplicates have different numbers of columns

I'm trying to specify the array of columns for Range.RemoveDuplicates by using a string like this:

Let's say there are 5 columns in this sheet

Dim Rng As Range
Dim i As Integer
Dim lColumn As Integer
Dim strColumnArray As String

    With ActiveSheet

        lColumn = Cells(1, Columns.Count).End(xlToLeft).Column

        strColumnArray = "1"
        For i = 2 To lColumn

            strColumnArray = strColumnArray & ", " & i

        Next i

    'String ends up as "1, 2, 3, 4, 5"

        Set Rng = Range(Cells(1, 1), Cells(1, lColumn).End(xlDown))
        Rng.RemoveDuplicates Columns:=Array(strColumnArray), Header:=xlNo

    End With

And I get the Run-Time error 13 Type Mismatch" error

So I read posts and saw where someone did it by specifying it as an array like this:

Dim Rng As Range
Dim i As Integer
Dim lColumn As Integer
Dim strColumnArray() As String

    With ActiveSheet

        lColumn = Cells(1, Columns.Count).End(xlToLeft).Column

        ReDim strColumnArray(lColumn) As String

        For i = 1 To lColumn + 1    'The array needs to start with 1

            strColumnArray(i) = i

        Next i

        Set Rng = Range(Cells(1, 1), Cells(1, lColumn).End(xlDown))
        Rng.RemoveDuplicates Columns:=strColumnArray, Header:=xlNo

    End With

I've tried it this way:

Rng.RemoveDuplicates(strColumnArray, Header:=xlNo)

amd this way:

Rng.RemoveDuplicates(Columns:=Array(strColumnArray), Header:=xlNo)

and in endless combinations of Variant, String, Integer, etc.

I must just be missing it at this point

any help would be appreciated

解决方案

I had to go back and re-read my post on this. I think the array has to be a Variant. Anyways, this seems to work:

Sub RemoveDupes()
Dim Rng As Range
Dim i As Integer
Dim lColumn As Integer
Dim ColumnArray As Variant

    With ActiveSheet
        lColumn = Cells(1, Columns.Count).End(xlToLeft).Column
        ReDim ColumnArray(lColumn - 1)
        For i = 0 To lColumn - 1  'The array needs to start with 1
            ColumnArray(i) = i + 1
        Next i
        Set Rng = Range(Cells(1, 1), Cells(1, lColumn).End(xlDown))
        Rng.RemoveDuplicates Columns:=(ColumnArray), Header:=xlYes
    End With
End Sub

这篇关于Excel 2013 VBA Range.RemoveDuplicates问题指定数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！