Looking for code to define a range

A

Ant

I am looking for code that automatically defines/names a range which changes
each month. Also I need the code which identifies the location of the last
cell in the range.
 
O

Otto Moehrbach

Not knowing what your range looks like I'll assume you want a range starting
at A1 and going down to the last occupied cell in Column A. I'll also
assume you want to name this range "MyRange".
To define and name the range, use:
Range("A1", Range("A" & Rows.Count).End(xlUp)).Name = "MyRange"
The cell address of the last occupied cell in Column A is:
Range("A" & Rows.Count).Address
If you want to use the range name in identifying the last cell of the range,
use:
Range("MyRange")(Range("MyRange").Count).Address
HTH Otto
 
A

Ant

Thanks Otto. That works well. If my range goes across to say column H, how do
I incorporate this into the formula below. I tried Range("A1", Range("A1:H1"
& Rows... but it debugged.
 
O

Otto Moehrbach

Range("A1", Range("H" & Rows.Count).End(xlUp)).Name = "MyRange"
HTH Otto
 
Top