VBA How to mark or clear duplicate rows?

[Tardisgx]

I have data in columns AB&C

To find duplicate rows I concatenated the 3 cells in each row in 1 cell so now column D is occupied and duplicates of column D's cells denotes the duplicate rows.

I want to keep 1 copy and clear the other duplicates (not delete rows but clear rows)

I have been using =IF(COUNTIF($D$2:$D$1048576,$D2)>1,"x","")

Which states the range to check against, and the single cell; if it is a duplicate show x otherwise be blank.

Unfortunately there is a character limit of 250 to which the above formula then shows VALUE! This is not good enough for my needs.

I need a helper column to mark the duplicates (so i can then "go to special" do shift left to select the whole rows) to right click and clear the relevant rows or a VBA solution that uses a helper column or not to achieve this goal.

 

[NoSparks]

Why not just use the built in Remove Duplicates tool found on the Data ribbon ?

 

[Fluff]

How about

Sub getUniques()
   Dim cl As Range
   Dim v As String
   
   With CreateObject("scripting.dictionary")
      For Each cl In Range("A2", Range("A" & Rows.count).End(xlUp))
         v = Join(Application.Index(cl.Resize(, 3).Value, 1, 0), "|")
         If Not .exists(v) Then
            .Add v, Nothing
         Else
            cl.Resize(, 3).ClearContents
         End If
      Next cl
   End With
End Sub

 

[Tardisgx]

Hi good question.

The action of deleting rows especially when they are not sequential is an extremely intensive one for excel. On my machine more often than not doing this results in a literal wait time to complete or just freezing and crashing.
I do not always deal with 10s of thousand of rows but it is possible and this vba addition i want would be part of a bigger macro of mine i use.

It is a lot quicker for rows to be cleared and then apply a filter, deselect blanks and copy and paste the data onto a new sheet. I only know this by having to "increase efficiency" of perfectly good macros (that used the command delete rows) that no longer worked quickly (or well; unreliable freezing and crashing) because the datasets i was given got larger; they need to use a different program than excel to be honest.

 

[Tardisgx]

Thank you Fluff this works, anyone reading I checked fluff's cleared rows against kutools own select first duplicate function, cleared the selected rows and compared the 2 outputs. They were the same.

 

[Fluff]

If you already had code that did what you needed, why did you waste our time asking for something

 

[Tardisgx]

If you already had code that did what you needed, why did you waste our time asking for something

Recording Kutools does nothing for the vba editor.

You could do a bunch of fancy tricks while recording and when you stop the recording to check; there is no code.

I hope that's clearer

 

[billandrew]

Fluff

If it means anything, I appreciate reading & learning Dictionaries from the post provided. I'm sure others are benefiting as well.

Thank you sir.