PDA

View Full Version : [SOLVED] Clear rows based on cell value



blackie42
06-19-2017, 08:29 AM
Hi,

Please could someone help with the following.

Row 11 has headers (column descriptors).

Column P (from P12) has various status descriptions.

I would like to delete entire rows (after P11) that has status "Clear" in column P

Struggling with the VBA code

Any help appreciated

thanks
Jon

YasserKhalil
06-19-2017, 08:33 AM
Hello
try this code


Sub Test()
Dim cell As Range, rng As Range, oRange As Range


Application.ScreenUpdating = False
Set rng = Range("P12:P" & Cells(Rows.Count, "P").End(xlUp).Row)

For Each cell In rng
If cell.Value = "Clear" Then
If oRange Is Nothing Then Set oRange = cell Else Set oRange = Union(oRange, cell)
End If
Next cell

If Not oRange Is Nothing Then oRange.EntireRow.Delete
Application.ScreenUpdating = True
End Sub

blackie42
06-20-2017, 02:41 PM
works great

many thanks
Jon

YasserKhalil
06-28-2017, 02:39 PM
You're welcome. Glad I can offer some help