Delete an entire row if a selected row doesn't have a specified value in a cell

[marcusdmc]

I am looking to delete an entire row if it doesn't have a cell with
certain criteria in it... for example in a cell the value would be in
a column: placed on hold
How could I create a macro that would delete every row that did not
have this value in it?
Thanks!

-Marcus

 

[Ron de Bruin]

Hi Marcus

Try this one for the activesheet
If the vale "ron" not exist it delete the row

Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1

With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

If Application.WorksheetFunction.CountIf(.Rows(Lrow), "ron") = 0 Then .Rows(Lrow).Delete
' Delete each row if the value "Ron" not exist in the row (It will look in the whole row)

Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub


See also
http://www.rondebruin.nl/delete.htm