Kevin,

You need to iterate through your selection, and based on a logic test
build up a second range object that you can then act upon. The
example below will delete the row where any cells in the selection are
even. Note that the selection doesn't need to be contiguous - you cna
build up the initial selection using Ctrl-clicks.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim R1 As Range
Dim R2 As Range
Dim myCell As Range

Set R1 = Selection 'or other range object, like Range("A1:A10")

For Each myCell In R1
If myCell.Value Mod 2 = 0 Then 'logic check
If R2 Is Nothing Then 'build up second range
Set R2 = myCell
Else
Set R2 = Union(R2, myCell)
End If
End If
Next myCell

MsgBox "The even values are in " & R2.Address(False, False)

'Do something to the newly created range

R2.EntireRow.Delete

End Sub



"Kevin McCartney" wrote in
message ...
Hi, need some help on how to retain range information for
several rows within a selection. Ok background, if a user
select several rows and then selects a user defind menu
option Delete Records. The delete process now needs to
potential delete all the selected rows but the procedure
needs to ensure that the record can is allowed to be
delete. Therefore I need to iterate through the rows
within the selection and retain the row (range) and after
analysing each record select those that can be delete and
then delete them all in one go. I can't delete them one by
one because Excel removes the entire row and moves the
rows up so if I delete the first record my second row in
my selection now becomes my first row. I've placed a
snippit of code below and where I need help relates to the
line 'Set rgeDelete= rgeCells' within the 'For Each
rgeCells' loop and then the code to select all the ranges
within rgeDelete and delete the entire rows.

Any help much appriciated.
regards
KM


Dim rgeSelection As Range
Dim rgeDelete As Range
Dim rgeCell As Range
Dim rgeEnd As Range

Set rgeSelection = Application.Selection
Set rgeEnd = Application.Cells.SpecialCells
xlCellTypeLastCell)

If rgeSelection.Rows.Count 1 Then
mstrFrmMsg = "Delete all selected deals?" & vbCrLf
& "Note that you are only allowed to delete deals that you
have entered in this session."
Select Case MsgBox(mstrFrmMsg, vbYesNo Or
vbInformation)
Case vbYes
For Each rgeCell In rgeSelection
If Not rgeCell.Cells(rgeCell.Row, rgeEnd.Column
+ 2).Value Then
Set rgeDelete= rgeCells
End If
Next rgeCell
rgeDelete.Select
Application.Selection.Delete
ActiveWorkbook.Save
ActiveWorkbook.Saved = True
Case vbNo
End Select
Else