Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Proper syntax for selecting a range using cells instead of range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Proper syntax for selecting a range using cells instead of range
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Proper syntax for selecting a range using cells instead of range
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Proper syntax for selecting a range using cells instead of 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Proper syntax for selecting a range using cells instead of range
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
syntax for countif when cells fall within a numerical range | Excel Worksheet Functions | |||
What is the proper syntex for .cells or .range ? | Excel Programming | |||
macro syntax for selecting variable range | Excel Discussion (Misc queries) | |||
Range of a whole column in Cells syntax | Excel Programming | |||
range and cells syntax | Excel Worksheet Functions |