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!