Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Statement Modifications | Excel Worksheet Functions | |||
turn off excel cell modifications? | Excel Discussion (Misc queries) | |||
webbrowser - excel - modifications | Excel Discussion (Misc queries) | |||
Disable Border and Other Format Modifications | Excel Programming | |||
Modifications to Permutation Macro | Excel Programming |