将数组分配给.RemoveDuplicates Columns:= [英] Assign Array to .RemoveDuplicates Columns:=

问题描述

我想删除保存数据的列范围内的重复行.

I want to delete duplicate rows in the range of columns which hold data.

我首先获得数据集使用的最后一行和最后一列

I first get the last row and the last column used by my data set

lastUsedRowDiff = resultBook.Sheets("Differences").Cells(resultBook.Sheets("Differences").Rows.Count, "A").End(xlUp).Row
lastUsedColumnDiff = resultBook.Sheets("Differences").Cells(6, resultBook.Sheets("Differences").Columns.Count).End(xlToLeft).Column

我尝试过使用RemoveDuplicate函数，如下所示:

I tried using the RemoveDuplicate function like this:

resultBook.Sheets("Differences").range(resultBook.Sheets("Differences").Cells(1, 1), resultBook.Sheets("Differences").Cells(lastUsedRowDiff, lastUsedColumnDiff)).RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44), _
Header:=xlNo

它可以工作，但是我希望列数是动态的，因此初始化以这种方式保存列的数组是不切实际的.

It works but I want the number of columns to be dynamic and therefore it is not practical to initialize the array holding the columns that way.

在调用RemoveDuplicates之前，我试图初始化一个包含列索引的数组，如下所示:

I tried to initialize an array holding the column indices prior to calling the RemoveDuplicates as follows:

ReDim columnArray(1 To lastUsedColumnDiff) As Integer
For p = 1 To lastUsedColumnDiff
    columnArray(p) = p
Next p

当我尝试将columnArray分配给Columns:=时出现错误.

When I try to assign the columnArray to Columns:= I get an error.

resultBook.Sheets("Differences").range(resultBook.Sheets("Differences").Cells(1, 1), resultBook.Sheets("Differences").Cells(lastUsedRowDiff, lastUsedColumnDiff)).RemoveDuplicates Columns:=columnArray, _
Header:=xlNo

下面是整个代码段:

lastUsedRowDiff = resultBook.Sheets("Differences").Cells(resultBook.Sheets("Differences").Rows.Count, "A").End(xlUp).Row
lastUsedColumnDiff = resultBook.Sheets("Differences").Cells(6, resultBook.Sheets("Differences").Columns.Count).End(xlToLeft).Column

ReDim columnArray(1 To lastUsedColumnDiff) As Integer
For p = 1 To lastUsedColumnDiff
    columnArray(p) = p
Next p

resultBook.Sheets("Differences").range(resultBook.Sheets("Differences").Cells(1, 1), resultBook.Sheets("Differences").Cells(lastUsedRowDiff, lastUsedColumnDiff)).RemoveDuplicates Columns:=columnArray, _
Header:=xlNo

推荐答案

好的，这很有趣...乍一看，我不明白为什么它不接受RemoveDuplicates方法调用. Google出现了此答案，建议使用VBA 在数组上运行，当我对其进行测试时，它似乎可以正常工作.

OK so this is interesting... At a glance, I don't understand why it won't accept an array argument for Columns in the RemoveDuplicates method call. Google turned up this answer, which suggests using the VBA Evaluate function on the array, and when I test it, it seems to work as expected.

问题似乎是RemoveDuplicates方法似乎认为 cols是某种函数.尝试使用valuate()方法来将其识别为变量.这在Excel 2007中对我有用，应该对您有用.让我知道.

The trouble seems to be that the RemoveDuplicates method seems to think that cols is a function of some sort. Try using the evaluate() method to get it to recognize it as a variable. This worked for me in Excel 2007 and should work for you. Let me know.

您的代码也可以从一些清理中受益，请尝试执行此操作，该代码使用With块使代码更易于阅读和阅读.修改:

Your code could also benefit from some cleaning up, try this, which uses With blocks to make the code easier to read & modify:

Sub foo()
Dim lastUsedRowDiff As Long
Dim lastUsedColumnDiff As Long
Dim myWorkbook As Workbook
Dim mySheet As Worksheet
Dim columnArray()
Dim p As Long

Set myWorkbook = ThisWorkbook       '## Modify as needed
Set mySheet = ThisWorkbook.Sheets(1) '## Modify as needed

With mySheet
    lastUsedRowDiff = .Cells(.Rows.Count, "A").End(xlUp).Row
    lastUsedColumnDiff = .Cells(6, .Columns.Count).End(xlToLeft).Column
End With

ReDim columnArray(1 To lastUsedColumnDiff)
For p = 1 To lastUsedColumnDiff
    columnArray(p) = p
Next

With mySheet
    .Range(.Cells(1, 1), .Cells(lastUsedRowDiff, lastUsedColumnDiff)).RemoveDuplicates _
        Columns:=Evaluate(columnArray), Header:=xlNo
End With

End Sub

这篇关于将数组分配给.RemoveDuplicates Columns:=的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！