modifications on the code

[George]

Hi, Group:

I need your help to modify my code. Here is what I expect:

In Column A under sheet1 with unknown rows, I always want to keep the
first 3 rows. For the rest other than the first 3 rows, I need a maco
to delete any rows if the cell value is NONE of "Apple", "Orange", or
"Banana".

Here is my code:

Sub DeleteRows()

Dim RangeColumnA As Range
Dim j As Range


With Worksheets("Sheet1")
Set RangeColumnA = .Range("A4", .Range("A" & Rows.Count).End(xlUp))
End With

Application.ScreenUpdating = False

For Each j In RangeColumnA
If NOT j.Value = "Apple" Or j.Value = "Orange" or j.Value =
"Banana" Then
j.EntireRow.Delete
End If
Next j

Application.ScreenUpdating = True

End Sub

Please advise!

George

 

[Rick Rothstein \(MVP - VB\)]

In just looking at your code (without actually testing it), this line looks
wrong for what you want to do...

If NOT j.Value = "Apple" Or j.Value = "Orange" or
j.Value = > "Banana" Then

I think you need parentheses around all of the tests so that the NOT
operator applies to the group...

If NOT (j.Value = "Apple" Or j.Value = "Orange" or j.Value => "Banana") Then

Rick

 

[George]

Hi, Rick:

You are right. Thanks.

However, there is one more problem: It looks like that the For
statement is not working properly. I need to press a macro button a
bunch of times to delete all rows which are not "Apple", "Orange", or
"Banana".

Would you please point out where is the problem?

Thanks again,

George

 

[Gary Keramidas]

if you're deleting rows, you need to start at the bottom of the range and work
your way up.