Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select a range
My worksheet has ROW 1 as header, and the data is variable.
Sometimes it is from A2:H10040, or sometimes it may be from A2:H9400. The rows vary but last column is always H. I wish to perform certain sorting, or text trimming etc based on certain values, which I can do using Macros. But how can I select the range before running the macros everytime I open the file, as the data size varies in rows. Scrolling down manually and selecting is difficult as Last data row varies in range A9000 to A11000 or so. How can I get a macro which will upon running will select the entire range and will give a name to it , say "myrange". Pl help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select a range
One of the many ways: This will select the used range except row no.1: Dim myRange As Range With Worksheets("Sheet1").UsedRange Set myRange = .Range(Cells(2, 1), Cells(.Rows.Count, ..Columns.Count)) End With If you want to select row no. 1 also then its as simple as: Set myRange = Worksheets("Sheet1").UsedRange Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select a range
Hi Sharad,
Unless "Sheet1" is the active sheet, I think that the line Set myRange = .Range(Cells(2, 1), Cells(.Rows.Count, Columns.Count)) will cause an error and should read: Set myRange _ = .Range(.Cells(2, 1), .Cells(.Rows.Count, .Columns.Count)) (dot added before each instance of Cells and before Columns.Count) --- Regards, Norman "Sharad" wrote in message ... One of the many ways: This will select the used range except row no.1: Dim myRange As Range With Worksheets("Sheet1").UsedRange Set myRange = .Range(Cells(2, 1), Cells(.Rows.Count, Columns.Count)) End With If you want to select row no. 1 also then its as simple as: Set myRange = Worksheets("Sheet1").UsedRange Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select a range
Oh, yes, I realize that I forgot the 'dot'.
Thanks. Sharad "Norman Jones" wrote in message ... Hi Sharad, Unless "Sheet1" is the active sheet, I think that the line Set myRange = .Range(Cells(2, 1), Cells(.Rows.Count, Columns.Count)) will cause an error and should read: Set myRange _ = .Range(.Cells(2, 1), .Cells(.Rows.Count, .Columns.Count)) (dot added before each instance of Cells and before Columns.Count) --- Regards, Norman "Sharad" wrote in message ... One of the many ways: This will select the used range except row no.1: Dim myRange As Range With Worksheets("Sheet1").UsedRange Set myRange = .Range(Cells(2, 1), Cells(.Rows.Count, Columns.Count)) End With If you want to select row no. 1 also then its as simple as: Set myRange = Worksheets("Sheet1").UsedRange Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I select a range? | Excel Worksheet Functions | |||
How can change range to select active rows instead of :=Range("S10 | Excel Discussion (Misc queries) | |||
When entering data into a range of cells, select the entire range. | Excel Discussion (Misc queries) | |||
Select Sheet then Select Range | Excel Programming | |||
Select Range as far as..... | Excel Programming |