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

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

On 22 Jul., 06:40, JasonK wrote:
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

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.

thanks Per, it works perfectly

you guys rock.

JasonK


On Tue, 21 Jul 2009 22:15:55 -0700 (PDT), Per Jessen
wrote:

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

On 22 Jul., 06:40, JasonK wrote:
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

thanks arjen van,


On Tue, 21 Jul 2009 22:23:01 -0700, arjen van...
wrote:

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.