Delete non-contiguous selected rows

  • Thread starter Thread starter Barbara Ryan
  • Start date Start date
B

Barbara Ryan

I need to be able to delete rows (via VBA) that a user selects on a
worksheet. The rows can be non-contiguous. I have seen several examples to
delete non-contiguous rows based on a cell value, but not for non-contiguous
selected rows. Any suggestions???

Thanks,
Barb Ryan
 
One way:

Copy the FUNCTION below into a standard code module. Call the following
function from your code like this:

Sub Test()

'your code
Call DeleteSelectedRows
'your other code

End Sub

Private Function DeleteSelectedRows()
'DELETE ENTIRE ROW FOR EACH ROW IN CURRENT SELECTION
Dim laRows() As String
Dim rRow As Range
Dim lX As Long
For Each rRow In Selection.Rows
lX = lX + 1
ReDim Preserve laRows(lX)
laRows(lX) = rRow.EntireRow.Address
Next rRow
For lX = UBound(laRows) To 1 Step -1
Range(laRows(lX)).EntireRow.Delete
Next lX
End Function

HTH
 
Please be aware that as written, the FUNCTION will only run from within the
module in which your other code resides. You can easily change this so that
the FUNCTION may reside in another module and still be called by changing the
key word "Private" to "Public".

HTH.
 
Ron,

I have sometimes found that my users may select a row more than once and
also may use mixed selections that can sometimes result in an error (1004
Cannot use that command on overlapping selections).

The method I posted resolves that...of course the OP doesn't specify this to
be an issue, but I posted my full blown resolution to head off that
possibility.

With my users you have to be ready for anything...
 
if the cells are already selected you can try either of the following:

Selection.Delete Shift:=xlUp

or

Selection.EntireRow.Delete Shift:=xlUp

-
I need to be able to delete rows (via VBA) that a user selects on a
worksheet. The rows can be non-contiguous. I have seen several
examples to
delete non-contiguous rows based on a cell value, but not for
non-contiguous
selected rows. Any suggestions???

Thanks,
Barb Ryan-
 
Maybe something like this would prove useful:

With ActiveSheet
Intersect(Selection.EntireRow, .Columns(1)).EntireRow.Delete
End With
 
Thanks so much --- worked like a charm! I was making this much more
complicated than it needed to be.....Barb
 
Thanks Dave, shorter and faster than mine...

Dave Peterson said:
Maybe something like this would prove useful:

With ActiveSheet
Intersect(Selection.EntireRow, .Columns(1)).EntireRow.Delete
End With
Click to expand...
 
Back
Top