Is there a way to batch update named ranges?

Afternoon everyone,

I have a spreadsheet where I created about 100 named ranges, they all start in column L and go to column AE. However, it turns out I didn't look far enough into the future when I was planning my spreadsheet and I now need to extend all of the ranges so that they go to column AM.

Is there a quick way to do this (bearing in mind I have 100 odd names)? or am I going to have to go into the name manager, work my way down the list and change AE to AM for every one?

Anyones help is much appreciated.

Thanks,

Alice

A short macro like this should do the job for you:

Sub ExtendRanges()

Const sCURRENT_END = "AE"
Const sNEW_END = "AM"

Dim nName As Name

For Each nName In ActiveWorkbook.Names
  nName.RefersTo = Replace(nName.RefersTo, sCURRENT_END, sNEW_END)
Next nName

End Sub

Thanks Andrew-R however when I try that I get a Runtime 1004 error saying that the name is not valid (and then the usual reasons, spaces, already exists, doesn't begin with a letter...)

It's almost like the code is renaming the actual name rather than the range - although I would have thought that's what the .refersto bit was supposed to do.

....any ideas?

Thanks,

---------- Post added at 01:32 PM ---------- Previous post was at 01:29 PM ----------

Bob - if I was going to use your method then I guess I would have to list out all my range names in, this bit

aryNames = Array("Bob", "Jim") ' extend as required

Also where does it specifiy where I would want to extend the range to?

Forgive me if these are stupid questions - VBA is all very new to me!

Thanks,

Originally Posted by qaliq

]Bob - if I was going to use your method then I guess I would have to list out all my range names in, this bit

aryNames = Array("Bob", "Jim") ' extend as required

That is so.

Originally Posted by qaliq

Also where does it specifiy where I would want to extend the range to?

Where it says

nameRefers.Resize(, nameRefers.Columns.Count + 1)

the original range is being resized by 1 column. You would need to change that to + 8 to increase from AE to AM.

Ok great, quick question then...is there a way that I could get excel to list out all my range names (as a continuous list rather than cell by cell like it would do if I did paste names)?

Thanks,

Cut some code

Dim aryNames As Variant
Dim nameRefers As Range
Dim refersValue As String
Dim i As Long

    aryNames = Array("Bob", "Jim") ' extend as required
    
    With ThisWorkbook
    
        For i = LBound(aryNames) To UBound(aryNames)
        
            Set nameRefers = .Names(aryNames(i)).RefersToRange
            Set nameRefers = nameRefers.Resize(, nameRefers.Columns.Count + 1)
            refersValue = "'" & nameRefers.Parent.Name & "'!" & nameRefers.Address

            .Names.Add Name:=aryNames(i), _
                       RefersTo:="=" & refersValue
        Next i
    End With

Dim namesList As String
Dim nme As Name

    With ThisWorkbook
    
        For Each nme In .Names
        
            namesList = namesList & """" & nme.Name & ""","
        Next nme
    End With
    
    Debug.Print Left(namesList, Len(namesList) - 1)

---------- Post added at 02:47 PM ---------- Previous post was at 02:45 PM ----------

If all names are to be updated, you could use

Dim nme As Name
Dim nameRefers As Range
Dim refersValue As String
    
    With ThisWorkbook
    
        For Each nme In .Names
        
            Set nameRefers = nme.RefersToRange
            Set nameRefers = nameRefers.Resize(, nameRefers.Columns.Count + 1)
            refersValue = "'" & nameRefers.Parent.Name & "'!" & nameRefers.Address

            .Names.Add Name:=nme.Name, RefersTo:="=" & refersValue
        Next i
    End With