CODEWITHSUNDEEP
excelvba
nigiroux
nigiroux

Reputation: 70

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.

Upvotes: 1

Views: 121

Answers (1)

Vityata
Vityata

Reputation: 43585

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

enter image description here

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:

enter image description here

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

Upvotes: 3

Related Questions