delete all rows except

[J.W. Aldridge]

need to delete all rows except those containing "apples" and or
"oranges" in column G.

code below works, just need to add second criteria.
assuming that "a" would be changed to "g".


Sub Delete_Row()
For i = Cells(Rows.Count, "a"). _
End(xlUp).Row To 2 Step -1
If InStr(UCase(Cells(i, "a")), "MZ")<1 _
Then Rows(i).Delete
Next i
End Sub

 

[Don Guillett]

Sub Delete_Row()
For i = Cells(Rows.Count, "a"). _
End(xlUp).Row To 2 Step -1
If InStr(UCase(Cells(i, "a")), "xxx")<1 _
or InStr(UCase(Cells(i, "a")), "yyy")<1 then _
Rows(i).Delete
Next i
End Sub

 

[Ron de Bruin]

Hi J.W. Aldridge

One way is to use AutoFilter
Test this on a copy of your workbook

I use <> in the filter so it not delete
DeleteValue1 and DeleteValue12

Sub Delete_with_Autofilter_Two_Criteria()
Dim DeleteValue1 As String
Dim DeleteValue2 As String
Dim rng As Range
Dim calcmode As Long

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

'Fill in the two values that you want to delete
DeleteValue1 = "apples"
DeleteValue2 = "oranges"

'Sheet with the data, you can also use Sheets("MySheet")
With ActiveSheet

'Firstly, remove the AutoFilter
.AutoFilterMode = False

'Apply the filter
.Range("G1:G" & .Rows.Count).AutoFilter Field:=1, _
Criteria1:="<>" & DeleteValue1, Operator:=xlAnd, Criteria2:="<>" & DeleteValue2

With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With

'Remove the AutoFilter
.AutoFilterMode = False
End With

With Application
.ScreenUpdating = True
.Calculation = calcmode
End With

End Sub

 

[J.W. Aldridge]

....unfortnately, this one didn't work.

it deleted all of the rows (although the criteria was met) and left no
rows existing.

I went back and ran the original code and it filtered and deleted it
correctly (but only one criteria of course).

any suggestions?

 

[J.W. Aldridge]

Pardon me...

Ron, the code you suggested did work.

Thanx both of you...

You always seem to be right there when we need ya!

 

[JLGWhiz]

I believe you wanted to use And instead of Or for the operator. By using
Or, if one is true then the entire If statement = true and it will delete.
So if you have Apples in one it would nor met the criteria, but the absence
of Oranges would would meet the criteria, so it would delete.
Using And would require that both Apples and Oranges be absent before it
deletes.