deleting rows based on a condition

[Danny54]

Can someone point me to a vba process that would spin thru column "A" and delete any row where only one (type) is found.
in the following there are two web01, web02 web03 and app03, so they would remain. Only one row is found for app01 and app02 so they would be deleted.


Suggestions would be appreciated.

Thanks

 

[Yongle]

Try this

Sub DeleteRows()
    Dim Cel As Range, uRng As Range
    Set uRng = Range("A" & Rows.Count)
    For Each Cel In Range("A1", uRng.End(xlUp))
        If WorksheetFunction.CountIf(Range("A:A"), Cel) = 1 Then Set uRng = Union(uRng, Cel)
    Next Cel
    uRng.EntireRow.Delete
End Sub

 

[Danny54]

Sweet.

can you briefly explain what the line does?

If WorksheetFunction.CountIf(Range("A:A"), Cel) = 1 Then Set uRng = Union(uRng, Cel)

I know were using the countif function whereas column A as the range. What's not clear is the rest of the line.


Thanks so much for your help!

 

[Yongle]

Did the code do what you wanted?

 

[Danny54]

Yes,
the code was exactly what i needed.

Thanks so much

 

[Yongle]

can you briefly explain what the line does?
I know were using the countif function whereas column A as the range. What's not clear is the rest of the line.

If WorksheetFunction.CountIf(Range("A:A"), Cel) = 1 Then Set uRng = Union(uRng, Cel)

I am using uRng to collect every cell in column A where the value in that cell appears only once
Using your data , and looping through cells starting at A1

A1 : the value appears twice in column A - DO NOTHING
A2 : same as A1
A3 : same as A1
A4 : same as A1
A5 : same as A1
A6 : same as A1
A7 : the value appears ONCE in column A - include cel A7 in uRng
A8 : same as A7 - include cel A8 in uRng
A9 : same as A1 - DO NOTHING
A10:same as A1

Then all rows included in uRng are deleted

 

[Danny54]

Thanks. That helps

Have a Great Day