VBA Range Names

Posted by Paul Johnson on January 08, 2002 5:50 PM

I posted earlier but the solution did not work optimally.

I need to find the most efficient way to select and name a range. The range could change.

For example:
A1:Z10
next time
A1:AB14000
and maybe next time
A1:K10000

Ideally the macro would name the exact range each time (no unnecessary cells).

Your help would be appreciated.

Posted by Thurio on January 08, 2002 6:38 PM

You just need a small adjustment to the code provided by Dan in answer to your last posting :-

Sheets("data").Range("A1").Select
ActiveSheet.UsedRange
ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=Range(Selection, Selection.SpecialCells(xlCellTypeLastCell))

Or alternatively :-

Sheets("data").Select
ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=ActiveSheet.UsedRange

Posted by paul on January 08, 2002 6:46 PM

Thank you

I will try it

: I posted earlier but the solution did not work optimally. : I need to find the most efficient way to select and name a range. The range could change. : For example