How do I define a range from sheet1!a2 to the last nonempty cell in the A
column
The name of the range should be equal to sheet!a1 ( the top most cell )

similarly for other columns b, c, d, e

With Worksheets("Sheet1")
set rng = .Range(.Range("A2"),.Cells(rows.count,1).End(xlup) )
End With


similarly for b, c, d, e

--
Regards,
Tom Ogilvy

"MAB" wrote in message
...
How do I define a range from sheet1!a2 to the last nonempty cell in the A
column
The name of the range should be equal to sheet!a1 ( the top most cell )

similarly for other columns b, c, d, e

Hi

Without VBA

=OFFSET(Sheet1!$A$2,,,COUNTIF(Sheet1!$A:$A)-1)
but you have to enter the name manually. And you mustn't have gaps in column
A.


Arvi Laanemets


"MAB" wrote in message
...
How do I define a range from sheet1!a2 to the last nonempty cell in the A
column
The name of the range should be equal to sheet!a1 ( the top most cell )

similarly for other columns b, c, d, e