Solved: deleting rows based on content of cells

[sunilmulay]

I'm a VBA newbie.
I want to set a macro to delete rows. There are two conditions however - see example below. If the user selects the last line (condition = if there is data in column D in the row containing the active cell), then the macro should delete the row as well as the 5 following rows. If the user selects the first line (condition=if there is no data in column D), then only that row should be deleted.
any ideas?
thanks! Sunil


CDE1WBS Level 2 Item1.1WBS Level 3 Item

[MaximS]

try this:
[VBA]
Sub DeleteAsClicked()

Dim LastRow As Long

LastRow = Range("D65536").End(xlUp).Row

If ActiveCell.Row = LastRow Then

Range("D" & LastRow & "" & LastRow + 5).EntireRow.Delete

End If

If ActiveCell.Row = 1 And ActiveCell.Value ="" Then

Range("D1").EntireRow.Delete

End If

End Sub[/VBA]

[sunilmulay]

Hi Maxim - again, I think I didn't write my question clearly enough....

I want to the delete row macro to do one of two things:
1. If there is a value in column B of the row that the user clicks in, I want the macro to delete that row as well as the one below.
2. If there is NO value in column B of the row that the user clicks in, I want the macro to delete the row as well as the following 5 rows.

Thanks
Sunil

[MaximS]

then use that code:

[VBA]Sub DeleteAsClicked()

Dim SelcetedRow As Long

SelcetedRow = ActiveCell.Row

If Cells(SelectedRow, 2).Value <> "" Then

Range("B" & SelectedRow & ":B" & SelectedRow + 1).EntireRow.Delete

Else

Range("B" & SelectedRow & ":B" & SelectedRow + 5).EntireRow.Delete

End If

End Sub[/VBA]

[sunilmulay]

Perfect!
Thanks
s