delete the rows for all cells in a column that are unique

[Tim Hanson]

Hello,

I need to delete the rows for all cells in column "E" that are unique, so I have only "NON-Unique" cells in column i.e Column "E" has only duplicates.

I have been searching for code to do this but have found very little.

Sorry I cant even post a snipit to start off with.

Thanks

 

[Marc L]

Hi,

not very clear …

Searching for code is a waste of time, worth is using brain !

This is my one shot code :​

Sub Demo()
    Application.ScreenUpdating = False
    For R& = Cells(Rows.Count, 5).End(xlUp).Row To 1 Step -1
        If Application.CountIf(Columns(5), Cells(R, 5)) = 1 Then Rows(R).Delete
    Next
    Application.ScreenUpdating = True
End Sub

In case the code not fits to your need, next time well explain it and attach a workbook …

Regards !
​

 

[Luke M]

Probably fastest way would be to create a helper column, fill with formula:
=COUNTIF(E:E,E2)=1
Filter on TRUE, delete all those rows.

If you need a macro, I'd use the same technique, just record yourself doing it.

 

[Luke M]

Hi Marc
Quick question, what does the ampersand after the R do here?
For R& = Cells(Rows.Count, 5).End(xlUp).Row To 1 Step -1

 

[Marc L]

Hi Luke,

it's the old fashion way to declare the type of variable on the run as you can read for Long …
Equals in VB Dim R as Long = …

Put a break point in the code and see it in the Local Variable window.

Regards !
​

 

[SirJB7]

@Luke M
Hi, buddy!
Young kid @Marc L still uses this:
http://chandoo.org/forum/threads/se...n-excel-to-outlook-recipent.15482/#post-94025
Let it be, let it be, ...
Regards!

 

[Marc L]

In this case, Let A be !

ReCarlsGardBerg !​

 

[Marc L]

See this : Macro to delete rows for all unique cells in a row

Seems to be a cross post ‼​