modifications on the code
 

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

modifications on the code
 

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" wrote in message
ps.com...
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

modifications on the code
 

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

modifications on the code
 

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

--


Gary


"George" wrote in message
ups.com...
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