Bulk Import Named Ranges

[smitpau]

Hi,

I have a list of names and range references to load into name manager.

Do you know if there is a way to import them in bulk.

For reference I've tried create from selection but it can only store values not the range reference.

Example of name to import

Name_1

=Database!$BF$1816:$DM$1822

Thanks,

Paul

 

[smitpau]

Just going to bump this once to see if anyone has any idea, any help appreciated as it's an annoying manual task.

 

[Joe4]

Where exactly does this list of names & ranges exist (what is the sheet name and range it exists in)?

 

[Joe4]

If your list was on Sheet 2, and your list is in columns A and B, starting on row 1, you could do something like this:

Sub AddNamedRanges()

    Dim sh As Worksheet
    Dim lr As Long
    Dim r As Long
    Dim nm As String
    Dim rng As Range
    
'   Specify worksheet that contains the range name list
    Set sh = Sheets("Sheet2")
    
'   Find last row on range name list with data (looking at column A)
    lr = sh.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows, starting at row 1
    For r = 1 To lr
'       Create range
        nm = sh.Cells(r, "A")
        Set rng = Range(Mid(sh.Cells(r, "B"), 2))
'       Build named range
        ActiveWorkbook.Names.add Name:=nm, RefersTo:=rng
    Next r

End Sub

The code could be shortened up a bit, but I broke things down using variables so it is easier to follow.

 

[smitpau]

Hi @Joe4

Thanks for the code.

Gave that a try and it inputs the column B data into the Value column in the Name Manager (like below) ideally this would instead be in the "Refers To" column. Appreciate easier said than done!

 

[Joe4]

It seemed to work correctly for me.

The column B values are text (string), right?
Do you have an equal sign at the beginning?
Does your entry look exactly (and I do mean EXACTLY, any minor thing like an extra space at the beginning or end can blow it up) like you show in your first post?

 

[smitpau]

Appreciate the help.

When I tried it originally it has a "Type Mismath" message popup and highlights the code as below.

It's a straight copy paste from the cells above (removed the Range and Mid part to get it to work whilst removing the = sign bit before)

 

[Joe4]

I am very confused by your images.
Do you have #REF errors in column B?
If so, that obviously would call errors and would not work.

Can you post your actual range that contains the sheets names and ranges, and tell us what sheet those are on?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[most]

As mentioned the second column needs to formatted as text and ...

Set rng = Range(sh.Cells(r, "B"))

...worked for me, not sure why MID was used in the example above.

ANYWAY, adding more complex reference I can't get to work. Like this...

=OFFSET(El!$A$2;COUNTA(El!$D:$D)-Chart_Electric_Months;0;Chart_Electric_Months;1)

I've changed these lines, but get error "Object required"

    Dim rng As String
     Set rng = sh.Cells(r, "B")

 

[Joe4]

As mentioned the second column needs to formatted as text and ...

Set rng = Range(sh.Cells(r, "B"))

...worked for me, not sure why MID was used in the example above.

ANYWAY, adding more complex reference I can't get to work. Like this...

=OFFSET(El!$A$2;COUNTA(El!$D:$D)-Chart_Electric_Months;0;Chart_Electric_Months;1)

I've changed these lines, but get error "Object required"

    Dim rng As String
     Set rng = sh.Cells(r, "B")

If my original code, "rng" was declared as a Range object, i.e.

Dim rng as Range

When setting the value of a range "object" (or any other "object"), you use the "Set" command, i.e.

Set rng = sh.Cells(r, "B")

However, you have declared "rng" to be a string, i.e.

    Dim rng As String

When setting the values of strings, you do NOT use the "Set" command, i.e.

    rng = sh.Cells(r, "B")

In this case, not that "rng" would not be a range at all. It is a string equal to value in that range.