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, Nov 01 '19 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, Nov 01 '19 at 19:37
If there is a duplicate, it is going to delete the duplicate and work just fine. But if there is no duplicate, then I get the error when I click the button. — nigiroux, Nov 01 '19 at 19:38
Step through the code and figure out what `lastrow` is when there are no duplicates. 1004 would typically indicate an invalid range. — Mike, Nov 01 '19 at 19:45
lastrow is the value of the last row with a value in it when I get the error. Which, I belive, is ok. — nigiroux, Nov 01 '19 at 19:51
@Mike - stepping through the code would not help, this is a minor "feature" in Excel. — Vityata, Nov 01 '19 at 20:05

Vityata · Accepted Answer · 2019-11-01T20:08:43.930

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 01 '19 at 20:08

answered Nov 01 '19 at 20:00

Vityata

42,633
8
55
100

1

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 – nigiroux Nov 01 '19 at 20:06

RemoveDuplicates return error if there's no duplicate

1 Answers1

Linked