Proper syntax for selecting a range using cells instead of range

[JasonK]

Thanks again for all the help in advance.


I am trying to select half a sheet and sort just that half. The sort
area wont always be the same exact size. Sometimes I'll need to sort
cells A20 - G418, and other times A21 - G418, or A22 .......

I know I can use a variable using cells to select a specific cell,
i.e., cells(counter,1) but I need to be able to use a variable to
select an entire range of cells.

Can someone explain to me the proper syntax for selecting a range like
cells(20,1) - cells(418,7) using cells instead of range?

Or perhaps, please explain to me how to select a Range using a
variable?

Either way would help me tremendously.

Thanks again for the help,

JasonK

 

[Per Jessen]

Hi JasonK

A few ways:

Range(Cells(20, 1), Cells(418, 7)).Select

Range("A"& FirstRow & ":G" & LastRow).Select

Range("A" & FirstRow, Range("G" & LastRow).Select

Cells(20,1).Resize(398,7).Select

And then I wouldn't select the range to sort as it is time consuming
to select ranges:

Range(Cells(20, 1), Cells(418, 7)).Sort Key1:=Range("A20"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Hopes this helps.
....
Per

 

[arjen van...]

If there's only one range of data on the sheet you can use something like:

Dim DataRange As Range
Set DataRange = Sheet1.UsedRange

Or if there's a certain point that the range begins, ends at, or contains
you can try:

Dim DataRange As Range
Set DataRange = Sheet1.Range("A20").CurrentRegion

This should work without having to create a variable as long as you don't
have a discontinuos range.

 

[JasonK]

thanks Per, it works perfectly

you guys rock.

JasonK

 

[JasonK]

thanks arjen van,