Naming Ranges w/ a Macro

Posted by Paul Johnson on January 07, 2002 3:56 PM

what is the best way to define and name a range w/ VBA

I am able to select my range like this:
Sheets("data").Range("k1").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select

But, what do I use as my Refer to:
ActiveWorkbook.Names.Add Name:="Actual_Qty_SUM", RefersToR1C1:= "=Data!R1C11:R1929C11"

The rows could change each time the user runs the macro.

Your help is appreciated ...

Posted by Thurio on January 07, 2002 4:09 PM

Range(ActiveCell, ActiveCell.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="Actual_Qty_SUM", RefersTo:="=Data!" & Selection.Address

Or one one line instead of two :-

ActiveWorkbook.Names.Add Name:="Actual_Qty_SUM", RefersTo:="=Data!" & Range(ActiveCell, ActiveCell.End(xlDown)).Address

Posted by Paul Johnson on January 08, 2002 5:17 AM

Thank you - it works perfectly !!!