Macro to Find and Delete Rows
Posted by Tyler on July 17, 2001 1:46 PM
I'm new to VBA and would like some help with a macro. The sheet that needs to be 'parsed' has cells that contain X, but each sheet can have a different number of cells that contain X. Here is what I tried.
Do
Cells.Find(What:="X", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete Shift:=xlUp
Loop Until Null
The 'Loop Until Null' statement was my feeble attempt to get the loop to end when no more occurrences of 'X' are found. I'd appreciate any help. Thanks, Tyler
Posted by Barrie Davidson on July 17, 2001 1:58 PM
Tyler, does your data have headers? Is "X" only found in one column?
Barrie
Posted by Tyler on July 18, 2001 5:49 AM
Barrie,
No headers on this sheet. Yes, "X" is found only in one column. Tyler
Posted by Barrie Davidson on July 18, 2001 6:31 AM
Tyler, this will loop through your data:
Do
Cells.Find(What:="X", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete Shift:=xlUp
Loop
You then need to have an error handling statement at the beginning of your macro. In my test I used:
On Error GoTo Lastline
and then put:
Lastline:
just before the Exit Sub statement.
The "On Error" statement tells Excel what to do when an error is encountered (not being able to find "X" for example). In my example, I am instructing Excel to go to the line labeled "Lastline" if it encounters an error (which ends the macro when it can't find "X").
Regards,
Barrie
Posted by Tyler on July 18, 2001 8:54 AM
Thanks Barrie!
