Delete row if "Not" macro
 

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

Delete row if "Not" macro
 

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Todd" wrote:

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

Delete row if "Not" macro
 

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).

Todd wrote:

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

--

Dave Peterson