MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Ben: More Conditional Row Selection


Posted by Cory on June 25, 2001 11:21 AM

I posted this in case you miss the one below:
Your code worked great for the row it specified based on the selected cell, but is there any way to delete not only that row but the four below it as well?
Thanks, Cory


Posted by Ben O. on June 25, 2001 11:30 AM

Re: More Conditional Row Selection

Cory,

This macro will delete the row & the next 4:

Sub ConditionalRowDelete()
Dim myRow As Integer
Dim myCol As Integer
Dim Counter As Integer
Counter = 0
myCol = ActiveCell.Column
cValue = ActiveCell.Value
rBegin = 2
' Replace 6000 with the last row of your data
rEnd = 6000
For myRow = rEnd To rBegin Step -1
Application.StatusBar = Counter & " rows deleted."
If Cells(myRow, myCol).Value = cValue Then
Range(Cells(myRow, myCol), Cells(myRow, myCol).Offset(4, 0)).EntireRow.Delete
Counter = Counter + 5
End If
Next myRow
Application.StatusBar = False
x = MsgBox(Counter & " rows deleted.", vbOKOnly, "Rows Deleted")
End Sub

The key piece of code is this:

Range(Cells(myRow, myCol), Cells(myRow, myCol).Offset(4, 0)).EntireRow.Delete

You can change the 4 to a higher or lower number in case it deletes too many or too few rows.

-Ben

Posted by Cory on June 25, 2001 11:45 AM

Ben: your an awesome man...

Like many others onthis board, you've made my "awesome" list! I knew I had to use offset in there somewhere, but I was wasn't sure of the syntax or placement.

Thanks for the help!