Select a range

[Guest]

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

 

[Sharad]

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

 

[Norman Jones]

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)

 

[Sharad Naik]

Oh, yes, I realize that I forgot the 'dot'.
Thanks.

Sharad