Delete duplicates BUT ignore Blank Cells.

[Sharid]

I want to delete any rows that have duplicates in Sheet3 column B to the last row with data in it starting from row 2.

However it needs to ignore any blank cells in column B and NOT delete them. As I tried excel's delete duplicates and it was taking the blank cells and also deleting them. I tried to write a vba code, but it would only look for duplicates until it reached the first blank cell. So if column B had data Row 1 to 100 and cell B30 was empty my code only check upto B2 to B30. I need one that would do the lot.

 

[Fluff]

Do you want to delete all dupes, or do you want to leave one instance of each?

 

[Sharid]

Hi Fluff
leave one instance of each. I need at least one record. However all rows that have a Blank/Empty Cell in Sheet3 column B are NOT deleted.

 

[Fluff]

Ok, how about

Sub Sharid()
   Dim Cl As Range, Rng As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Sheets("sheet3").Range("B2", Sheets("sheet3").Range("B" & Rows.Count).End(xlUp))
         If Cl <> "" Then
            If Not .Exists(Cl.Value) Then
               .Add Cl.Value, Nothing
            Else
               If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
            End If
         End If
      Next Cl
   End With
   If Not Rng Is Nothing Then Rng.EntireRow.Delete
End Sub

 

[Sharid]

Cheers Fluff, that was super

 

[Fluff]

You're welcome & thanks for the feedback.