deleting rows based on one cell's contents

[invisigirl]

How can I delete, say, 3 or 4 rows based on the contents of what's in one cell of the first row in that series? For example:

Cell A1 says 4/11/02. I want to delete rows 1-5, and also the same number of rows beneath any instance where "4/11/02" appears in column A.

Can you help? Thanks!

 

[BobUmlas]

Sub Deleter()
del = InputBox("Text to delete + 4 rows?", , ActiveCell.Value)
On Error Resume Next
1:
Set rw = Columns(1).Find(del, lookat:=xlWhole)
If Not rw Is Nothing Then
rw.Resize(5).Delete
GoTo 1
End If
End Sub

 

[invisigirl]

We'll see if that works (it's not for me).

Thanks much!

 

[invisigirl]

Wow, that didn't work at all. Either I'm retarded (distinct possibility), it doesn't work in Excel 97 (don't know why that would happen), or there's some other issue.

VBS is exceedingly foreign to me. The following script was posted in another thread I found that only deleted the row holding the offending text. They look very different. If my idiocy doesn't annoy too much, could someone explain the difference?

Sub del_rows()
While ActiveCell.Value = "?"
If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
ActiveCell.Offset(1, 0).EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Wend
End Sub


Oh, and yes, I am replacing the question marks and such with the correct information. I am almost, but not quite, that dumb.

 

[fght]

The macro posted by B. Umlas does what you asked for.

Presumably, you are looking to delete a variable number of rows rather than a fixed number such as 5 per the macro.

Try this :-

Sub Deleter()
del = InputBox("Text to delete", , ActiveCell.Value)
On Error Resume Next
1:
Set rw = Columns(1).Find(del, after:=[A65536], lookat:=xlWhole)
If Not rw Is Nothing Then
rw.EntireRow.Delete
GoTo 1
End If
End Sub

If this does not do what you want, perhaps you should post some more detailed info as to what you need (some before and after data).