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 |
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 |
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. |
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 |
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. |
All times are GMT +1. The time now is 07:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com