deleting rows based on one cell's contents

invisigirl

Board Regular
Joined
Mar 18, 2002
Messages
130
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!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
 
Upvote 0
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. :)
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top