VBA Range Syntax Range(A2:???????) where ??? is Last used cell Col

[Guest]

Using XL 2003 & 97

What is the correct syntax for Range() as per the Subject Line?

The following works fine:

Set MyRange =
Worksheets("Sheet1").Range("A2:A10000").SpecialCells(xlCellTypeBlanks)

But, I want Range("A2:A10000") to be A2 : to the Last used cell Col A

Any thoughts?

TIA

Dennis

 

[Dave Peterson]

How about:

with worksheets("sheet1")
set myrange = .range("a2",.cells(.rows.count,"A").end(xlup))
end with

You can add the .specialcells() stuff in that line or later.

 

[Peo Sjoblom]

..Cells(.Rows.Count, "A").End(xlUp).Row


Regards,

Peo Sjoblom