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

You can use
Range("B11").CurrentRegion



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


"katmando" <katmando.27ymnm_1147883102.9015@excelforum-nospam.com> wrote in message
news:katmando.27ymnm_1147883102.9015@excelforum-nospam.com...
>
> 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
>

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

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

Thanks for those...
Ended up using Daves one

Cheers
K