Expert: Bob Umlas - 9/27/2004

Question
Hi Bob,

You have been very impressive with your replies in the past. Try this one! Is it possible to delete rows by formula, as against deleting rows manually?

Here's my actual example: I dump thousands of rows into a spreadsheet as a result of a database query. Following is a small portion:

14/U13/01/10XX
--------------
60

(1 row(s) affected)

14/U13/01/20XX
--------------
60

(1 row(s) affected)

14/U13/01/30XX
--------------
0

(1 row(s) affected)

I would like to get rid of certain rows as superfluous:
1) where the cell says "(1 Row(s) affected)" I would like to delete this and, even better, the (blank) rows above and below it
2) where cell says "0" and only "0", I would like to get rid of this and the two lines above it - e.g. in this example, where the cell says "0", the lines to remove would be the "0" row itself, the "14/U13/01/30XX" row, and the row with the dotted lines in it

Sounds like a tall order to me, but see what you think!

Many thanks in anticipation

Dave


Answer
You would need VBA code to do it. If your data is in column A this (untested) macro should work:
Sub DeleteWeird()
  For i=Range("A65536").end(xlup).row to 3 step -1
    if cells(i,1).value="(1 Row(s) affected)" then
       rows(i-1).resize(3).Delete
    elseif cells(i,1).value="0" then
       rows(i-2).resize(3).Delete
    End If
  Next
End Sub