Delete row if "Not" macro

[Todd]

Hello, I have a macro that deletes a row if it contains certain text in a
cell. I need to convert it to delete every row that does not have the
certain text in a cell.

So for the selected range, delete every row that does not have a cell that
says "cost".

Thanks,

Todd.


Sub DeleteRowifText()
Dim rng As Range
Dim what As String
what = "cost"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If
Loop
End Sub

 

[chijanzen]

Todd:

try

Sub DeleteRowifText()
Dim rng As Range
Dim what As String
what = "cost"
For i = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Rows(i), what) >= 1 Then
Rows(i).Delete
'or Rows(i).Delete Shift:=xlUp
End If
Next i
End Sub
Function LastRow() As Integer
LastRow = Cells.Find(what:="*", After:=[A1],
SearchDirection:=xlPrevious).Rows.Row
End Function

 

[Dave Peterson]

One way:

Option Explicit
Sub DeleteRowifNoText()
Dim rng As Range
Dim whatToFind As String
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

whatToFind = "cost"
With ActiveSheet
FirstRow = 1 'no headers?
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
With .Rows(iRow)
Set rng = .Find(what:=whatToFind, after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchorder:=xlByColumns, _
searchdirection:=xlNext, MatchCase:=False)
End With
If rng Is Nothing Then
.Rows(iRow).Delete
End If
Next iRow
End With
End Sub

I used column A to find the last used row.

And I specified the parms to the .find statement. If you don't specify them,
excel will use the same parms as the previous Find (either from code or by the
user).