如果没有重复，则RemoveDuplicates返回错误 [英] RemoveDuplicates return error if there's no duplicate

问题描述

下面显示的代码来自此示例:

Code shown below is derived from this example: excel: check for duplicate rows based on 3 columns and keep one row

Sub Testing()

Dim lastrow As Long

    With ThisWorkbook.Worksheets(1)
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lastrow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            lastrow = 1
        End If            

        .Range("A1:M" & lastrow).RemoveDuplicates Columns:=Array(1, 4, 5, 6, 7, 11), _
            Header:=xlYes               
    End With 
End Sub

当有重复项时，代码可以正常工作.但是，如果没有，则返回错误.因此，我想知道是否有一种方法可以快速计算重复项的数量，也许要加上条件或类似的东西.

The code work fine when there are duplicates. But if there are not, it return an error. So I was wondering if there is a way to quickly count the number of duplicates to ,maybe, put a condition or something like that.

推荐答案

恭喜！这似乎是Excel的错误，当过滤没有初始值的列时会出现该错误.要复制，请在新 Excel工作表上写以下数据(新部分很重要):

Congrats! This seems to be a bug by Excel, which appears when a column with no initial value is being filtered. Pretty much, to replicate, write the following data on a new excel worksheet (the new part is important):

然后运行代码并获得错误1004.以某种方式，VBA不满意对列7和11进行过滤，尽管默认情况下它们中没有数据.然后将字段的大小增加到 M :

Then run the code and get error 1004. Somehow, VBA is not happy that columns 7 and 11 are being filtered, although there is no data in them by default. Then increase the size of the field up to M:

然后再次运行.有用.现在，如第一张图片所示，删除数据.再次运行.可以.

Then run it again. It works. Now delete the data, as in the first picture. Run it again. It works.

这篇关于如果没有重复，则RemoveDuplicates返回错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！