Creating a Named Range within a macro (Excel 2000 SR1)

Topic

New Reply

I want to have a portion of one of my macros update/recreate a named range called DB

I’ve recorded the code below but don’t know how to change it so that it doesn’t point to a specific range (the RC addresses). Can someone help me?

Selection.CurrentRegion.Select
ActiveWorkbook.Names.Add Name:=”DBTest”, RefersToR1C1:= _
“=Accounts!R1C7:R163C60”

Thanks in advance,

Christa

Reply | Quote

Replies

Does

    ActiveWorkbook.Names.Add Name:="DBTest", _
        RefersTo:=ActiveCell.CurrentRegion

do what you want ?

Andrew C

Reply | Quote

Andrew,

Yes, it does…thanks so much for all your help!

Reply | Quote

However, be careful. If the range you want to be named is contiguous with other cells that you don’t want the range name to cover, the CurrentRegion will expand to include them as well. If you use this code, make sure the entire range stays isolated from other blocks.

If that is not possible, other approaches are necessary, and the details depend on the situation.

Reply | Quote

Or an alternative

    ActiveCell.CurrentRegion.Name = "DbTest"

Andrew C

Reply | Quote