下面显示的代码来自此示例: 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.
更多推荐
如果没有重复,则RemoveDuplicates返回错误
发布评论