如果没有重复，则RemoveDuplicates返回错误

本文介绍了如果没有重复，则RemoveDuplicates返回错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

下面显示的代码来自此示例: excel:检查基于3列的重复行，并保留一行

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.