Delete non-contiguous selected rows
 

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

Delete non-contiguous selected rows
 

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

"Barbara Ryan" wrote:

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

Delete non-contiguous selected rows
 

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.

"XP" wrote:

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

"Barbara Ryan" wrote:

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

Delete non-contiguous selected rows
 

Why not use

Selection.EntireRow.Delete



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Barbara Ryan" wrote in message news:2B48g.9444$B42.2788@dukeread05...
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

Delete non-contiguous selected rows
 

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...

"Ron de Bruin" wrote:

Why not use

Selection.EntireRow.Delete



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Barbara Ryan" wrote in message news:2B48g.9444$B42.2788@dukeread05...
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

Delete non-contiguous selected rows
 

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-


--
Ikaabod
------------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371
View this thread: http://www.excelforum.com/showthread...hreadid=540379

Delete non-contiguous selected rows
 

Maybe something like this would prove useful:

With ActiveSheet
Intersect(Selection.EntireRow, .Columns(1)).EntireRow.Delete
End With



XP wrote:

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...

"Ron de Bruin" wrote:

Why not use

Selection.EntireRow.Delete



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Barbara Ryan" wrote in message news:2B48g.9444$B42.2788@dukeread05...
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






--

Dave Peterson

Delete non-contiguous selected rows
 

Thanks so much --- worked like a charm! I was making this much more
complicated than it needed to be.....Barb



"Ikaabod" wrote in
message ...

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-


--
Ikaabod
------------------------------------------------------------------------
Ikaabod's Profile:
http://www.excelforum.com/member.php...o&userid=33371
View this thread: http://www.excelforum.com/showthread...hreadid=540379

Delete non-contiguous selected rows
 

Thanks Dave, shorter and faster than mine...

"Dave Peterson" wrote:

Maybe something like this would prove useful:

With ActiveSheet
Intersect(Selection.EntireRow, .Columns(1)).EntireRow.Delete
End With



XP wrote:

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...

"Ron de Bruin" wrote:

Why not use

Selection.EntireRow.Delete



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Barbara Ryan" wrote in message news:2B48g.9444$B42.2788@dukeread05...
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






--

Dave Peterson