select a range by cell names; vba
 

I'm trying to select X rows for a sort. The first and last rows have
a defined name, RSTART and RLAST. They look
like this in the Define Name dialog: ='MySheet'!$15:$15 ....

How do you select the rows using these names. Here's the macro output
of my best shot. I can't figure out how to incorporate the second
name. Thank you.

Sub trythis()

Application.Goto Reference:="RSTART"
Rows("15:59").Select '<---- Held down shift key and selected
RLAST in cell address combo
Range("I15").Activate ' what is this?
Selection.Sort Key1:=Range("B15"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub

select a range by cell names; vba
 

Most things that you do in excel don't need to have the objects (like ranges or
worksheets) selected first.

You could use code like:

Option Explicit
Sub trythis()

Dim wks As Worksheet
Dim myRng As Range

Set wks = ActiveSheet

With wks
'I like this first line--I find it more self-documenting
Set myRng = .Range(.Range("RStart"), .Range("RLast"))
'but you could use either of these, too.
'Set myRng = .Range("Rstart", "rlast")
'Set myRng = .Range("Rstart:Rlast")
End With

With myRng
.Sort Key1:=.Columns(2), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With

End Sub

cate wrote:

I'm trying to select X rows for a sort. The first and last rows have
a defined name, RSTART and RLAST. They look
like this in the Define Name dialog: ='MySheet'!$15:$15 ....

How do you select the rows using these names. Here's the macro output
of my best shot. I can't figure out how to incorporate the second
name. Thank you.

Sub trythis()

Application.Goto Reference:="RSTART"
Rows("15:59").Select '<---- Held down shift key and selected
RLAST in cell address combo
Range("I15").Activate ' what is this?
Selection.Sort Key1:=Range("B15"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub

--

Dave Peterson

select a range by cell names; vba
 

On Nov 21, 7:42*am, Dave Peterson wrote:
Most things that you do in excel don't need to have the objects (like ranges or
worksheets) selected first.

You could use code like:

Option Explicit
Sub trythis()

* * Dim wks As Worksheet
* * Dim myRng As Range

* * Set wks = ActiveSheet

* * With wks
* * * * 'I like this first line--I find it more self-documenting
* * * * Set myRng = .Range(.Range("RStart"), .Range("RLast"))
* * * * 'but you could use either of these, too.
* * * * 'Set myRng = .Range("Rstart", "rlast")
* * * * 'Set myRng = .Range("Rstart:Rlast")
* * End With

* * With myRng
* * * * .Sort Key1:=.Columns(2), Order1:=xlDescending, _
* * * * * * Header:=xlGuess, OrderCustom:=1, _
* * * * * * MatchCase:=False, Orientation:=xlTopToBottom, _
* * * * * * DataOption1:=xlSortNormal
* * End With

End Sub



cate wrote:

I'm trying to select X rows for a sort. *The first and last rows have
a defined name, RSTART and RLAST. *They look
like this in the Define Name dialog: *='MySheet'!$15:$15 ....

How do you select the rows using these names. *Here's the macro output
of my best shot. *I can't figure out how to incorporate the second
name. *Thank you.

Sub trythis()

* * Application.Goto Reference:="RSTART"
* * Rows("15:59").Select * *'<---- Held down shift key and selected
RLAST in cell address combo
* * Range("I15").Activate * *' what is this?
* * Selection.Sort Key1:=Range("B15"), Order1:=xlDescending,
Header:=xlGuess _
* * * * , OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
* * * * DataOption1:=xlSortNormal

End Sub

--

Dave Peterson

Thank you. I always have problems with ranges. Sometimes I have
numbers, sometimes letters and, this time, names. Ahhhh.
Thanks again.

select a range by cell names; vba
 

Sub sortnamedrange()
'NOT needed unless a different sheet
'Application.Goto Reference:="RSTART"

mc = 2 ' col B
fr = Range("rstart").Row
lr = Range("rlast").Row
Range(Cells(fr, mc), Cells(lr, mc)) _
.Sort Key1:=Cells(fr, mc), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"cate" wrote in message
...
I'm trying to select X rows for a sort. The first and last rows have
a defined name, RSTART and RLAST. They look
like this in the Define Name dialog: ='MySheet'!$15:$15 ....

How do you select the rows using these names. Here's the macro output
of my best shot. I can't figure out how to incorporate the second
name. Thank you.

Sub trythis()

Application.Goto Reference:="RSTART"
Rows("15:59").Select '<---- Held down shift key and selected
RLAST in cell address combo
Range("I15").Activate ' what is this?
Selection.Sort Key1:=Range("B15"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub