RemoveDuplicates return error if there's no duplicate

Question

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.

Testing just this portion works fine for me. Is there anything else past the With statement that you haven't included? — Mike
– Mike, Commented Nov 1, 2019 at 19:33
No, there is nothing after that line. The error I get is 1004 and on the line of .Range("A1:M" .... — nigiroux
– nigiroux, Commented Nov 1, 2019 at 19:37
Step through the code and figure out what lastrow is when there are no duplicates. 1004 would typically indicate an invalid range. — Mike
– Mike, Commented Nov 1, 2019 at 19:45
@Mike - stepping through the code would not help, this is a minor "feature" in Excel. — Vityata
– Vityata, Commented Nov 1, 2019 at 20:05

Vityata · Accepted Answer · 2019-11-01 20:08:43Z

3

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):

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.

edited Nov 1, 2019 at 20:08

answered Nov 1, 2019 at 20:00

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nigiroux Over a year ago

Yea, with what you just said I found the problem. I had a table from line 1 to 4 with values up to M. But in rows 5 and 6 I only had value past M so i got the error. Thank you

Collectives™ on Stack Overflow

RemoveDuplicates return error if there's no duplicate

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related