selecting Variable range
 

I'm using the code below to sort some data.


Range("B11:R35").Select
Selection.sort Key1:=Range("N11"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal




Rather than having to change the range for the data i want to sort i
would like to be able to find the lower limit. The beginning of the
range will always be B11 and the end will always be somewhere in column
"R"

I'm sure I've seen this done using an "end" argument

Note some of the columns in the range will be blank but none of the
rows will be


--
katmando
------------------------------------------------------------------------
katmando's Profile: http://www.excelforum.com/member.php...o&userid=29803
View this thread: http://www.excelforum.com/showthread...hreadid=542982

selecting Variable range
 

You can use
Range("B11").CurrentRegion



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


"katmando" wrote in message
...

I'm using the code below to sort some data.


Range("B11:R35").Select
Selection.sort Key1:=Range("N11"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal




Rather than having to change the range for the data i want to sort i
would like to be able to find the lower limit. The beginning of the
range will always be B11 and the end will always be somewhere in column
"R"

I'm sure I've seen this done using an "end" argument

Note some of the columns in the range will be blank but none of the
rows will be


--
katmando
------------------------------------------------------------------------
katmando's Profile: http://www.excelforum.com/member.php...o&userid=29803
View this thread: http://www.excelforum.com/showthread...hreadid=542982

selecting Variable range
 

Hi,

range("b11:r11").end(xldown).sort Key1:=Range("N11"),
Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

in case that you need select columns b..r and you have empty columns
between them, or non-empty cell in p11 (and following to the right).

Regards,
Ivan

selecting Variable range
 

I like this:

dim LastRow as long
dim myRng as Range

with activesheet
'use column R to find the last row???
lastrow = .cells(.rows.count,"R").end(xlup).row
set myrng = .range("b11:R" & lastrow)

with myrng
'.columns(13) since column A is not included
'13th column in B:R
.sort key1:=.columns(13), ...
end with

end with



katmando wrote:

I'm using the code below to sort some data.

Range("B11:R35").Select
Selection.sort Key1:=Range("N11"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

Rather than having to change the range for the data i want to sort i
would like to be able to find the lower limit. The beginning of the
range will always be B11 and the end will always be somewhere in column
"R"

I'm sure I've seen this done using an "end" argument

Note some of the columns in the range will be blank but none of the
rows will be

--
katmando
------------------------------------------------------------------------
katmando's Profile: http://www.excelforum.com/member.php...o&userid=29803
View this thread: http://www.excelforum.com/showthread...hreadid=542982

--

Dave Peterson

selecting Variable range
 

Thanks for those...
Ended up using Daves one

Cheers
K


--
katmando
------------------------------------------------------------------------
katmando's Profile: http://www.excelforum.com/member.php...o&userid=29803
View this thread: http://www.excelforum.com/showthread...hreadid=542982