selecting Variable range

[katmando]

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

 

[Ron de Bruin]

You can use
Range("B11").CurrentRegion

 

[Ivan Raiminius]

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

 

[Dave Peterson]

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]

Thanks for those...
Ended up using Daves one

Cheers
K