Delete Multiple Rows based on Selection

[Kevin O'Neill]

I want to be able to select cells B2:B9 (or any cells in order in a
coloumn), and use a macro to delete rows 2 - 9 starting with row 9 and
going to row 2 using a loop. I have a routine written for deleting 1
row, if 1 cell is selected. How would I use that routine to delete
multiple rows?

For i = 0 To tis - 13
If Range("B" & 11 + i).Value = activecell.Value Then
Range("A" & 11 + i & ":H" & 11 + i).Delete Shift:=xlUp
activecell.Select
Exit Sub
End If
Next i

 

[PaulD]

: I want to be able to select cells B2:B9 (or any cells in order in a
: coloumn), and use a macro to delete rows 2 - 9 starting with row 9 and
: going to row 2 using a loop. I have a routine written for deleting 1
: row, if 1 cell is selected. How would I use that routine to delete
: multiple rows?
:
: For i = 0 To tis - 13
: If Range("B" & 11 + i).Value = activecell.Value Then
: Range("A" & 11 + i & ":H" & 11 + i).Delete Shift:=xlUp
: activecell.Select
: Exit Sub
: End If
: Next i
:

give this a try

Public Sub test()
Dim c As Range
Dim i As Long
Dim OldRow As Long
Dim DeleteRow As New Collection

For Each c In Selection
If c.Row <> OldRow Then
DeleteRow.Add c.Row
End If
OldRow = c.Row
Next c
For i = DeleteRow.Count To 1 Step -1
Rows(DeleteRow.Item(i)).Delete
Next i
End Sub

Paul D

 

[Ron de Bruin]

Hi Kevin

Why you want a loop ?
Try this

Sub test()
If Selection.Columns.Count > 1 Then Exit Sub
firstrow = Selection.Cells(1).Row
lastrow = Selection.Cells(Selection.Cells.Count).Row
For rw = lastrow To firstrow Step -1
Rows(rw).Delete
Next
End Sub

 

[Kevin O'Neill]

Looks like I got a little side tracked, I'll have to get back this
another day, thanks for the responses.