Naming Current Region
 

Hi there,
Can someone please help with a simple macro query I have.
I need to add a name to the current region selected, such
that I can use this name in later actions. The region can
vary in size and hence I can't reference a cell range.
I'm a bit of a beginner when it comes to macros in Excel,
however I would have thought that I needed a command such
as: ActiveWorkbook.Names.Add Name:="The Name",
RefersTo:CurrentRegion
or somesuch. Any advice would be appreciated.

Naming Current Region
 

Selection.Name = "TheName"

or

Selection.Name = "The_Name"


(Can't have a space in a the name of a name)

--

John Green - Excel MVP
Sydney
Australia


"Gavin" wrote in message ...
Hi there,
Can someone please help with a simple macro query I have.
I need to add a name to the current region selected, such
that I can use this name in later actions. The region can
vary in size and hence I can't reference a cell range.
I'm a bit of a beginner when it comes to macros in Excel,
however I would have thought that I needed a command such
as: ActiveWorkbook.Names.Add Name:="The Name",
RefersTo:CurrentRegion
or somesuch. Any advice would be appreciated.

Naming Current Region
 

Hi Gavin,
try this

sub test()
ActiveWorkbook.Names.Add Name:="test" RefersTo:=ActiveCell.CurrentRegion
end sub

Remember the CurrentRegion must be bounded by blank rows and columns.

Regards,
Nigel

"Gavin" wrote in message ...
Hi there,
Can someone please help with a simple macro query I have.
I need to add a name to the current region selected, such
that I can use this name in later actions. The region can
vary in size and hence I can't reference a cell range.
I'm a bit of a beginner when it comes to macros in Excel,
however I would have thought that I needed a command such
as: ActiveWorkbook.Names.Add Name:="The Name",
RefersTo:CurrentRegion
or somesuch. Any advice would be appreciated.

Naming Current Region
 

A possibly easier approach is as posted in this thread by John Green. To
create the same name:

ActiveCell.CurrentRegion.Name = "test"

--
Regards,
Tom Ogilvy


"Nigel Brown" wrote in message
om...
Hi Gavin,
try this

sub test()
ActiveWorkbook.Names.Add Name:="test"
RefersTo:=ActiveCell.CurrentRegion
end sub

Remember the CurrentRegion must be bounded by blank rows and columns.

Regards,
Nigel

"Gavin" wrote in message
...
Hi there,
Can someone please help with a simple macro query I have.
I need to add a name to the current region selected, such
that I can use this name in later actions. The region can
vary in size and hence I can't reference a cell range.
I'm a bit of a beginner when it comes to macros in Excel,
however I would have thought that I needed a command such
as: ActiveWorkbook.Names.Add Name:="The Name",
RefersTo:CurrentRegion
or somesuch. Any advice would be appreciated.