[Solved] VBA: Name a region

Jas · Oct 27th 2003

I'd like the macro to name a region. Whenever the macro is run, it will start in cell B9, select the current region (list), and then define that range as a named region. When I attempt to record this macro, it records the specific cells that the current list occupies. The problem with this method is that sometimes the list will be larger or smaller, so I need the macro to be based off the CurrentRegion (as selected in step two below).

What can I use besides "RefersToR1C1:="='Individual Pivots'!R8C2:R11C5" to accomplish this?

Current code:

Range("B9").Select
Selection.CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="namedregion", RefersToR1C1:= _
"='Individual Pivots'!R8C2:R11C5"

Anonymous · Oct 27th 2003

This is a case where the macro recorder is not very helpful. The following one line should do it.
Range("B9").currentregion.name="NamedRegion"

Richie(UK) · Oct 27th 2003

Hi Jas,

Try :

Code

Sub Test()
    
    ActiveWorkbook.Names.Add _
        Name:="namedregion", _
        RefersTo:="='Individual Pivots'!" & _
        Worksheets("Individual Pivots").Range("B9").CurrentRegion.Address


End Sub

HTH

Jas · Oct 27th 2003

Thanks, gentlemen, working like a charm.

[Solved] VBA: Name a region

Participate now!

Share