PDA

View Full Version : Solved: deleting rows based on content of cells



sunilmulay
10-06-2008, 03:23 AM
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 Item 1.1WBS Level 3 Item

MaximS
10-06-2008, 03:46 AM
try this:

Sub DeleteAsClicked()

Dim LastRow As Long

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

If ActiveCell.Row = LastRow Then

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

End If

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

Range("D1").EntireRow.Delete

End If

End Sub

sunilmulay
10-06-2008, 04:06 AM
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
10-06-2008, 04:17 AM
then use that code:

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

sunilmulay
10-06-2008, 04:46 AM
Perfect!
Thanks
s