Renaming named ranges

Question

I have a workbook with many named ranges to rename. I have a spreadsheet with the old names and the new names.

This works:

Dim strOldName As String
Dim strNewName As String            

strOldName = rngNamedRanges.Cells(1, 6).Value2
strNewName = strOldName & "_Renamed"

With ActiveWorkbook.Names(strOldName)
    .Name = strNewName
End With

This does not:

Dim strOldName As String
Dim strNewName As String            

strOldName = rngNamedRanges.Cells(1, 6).Value2
strNewName = CStr(rngNamedRanges.Cells(1, 8).Value2)

With ActiveWorkbook.Names(strOldName)
    .Name = strNewName
End With

Clearly, I'm doing something wrong assigning strNewName.

I have also tried using .text, .value, and trimming the string, all with the same non-result.

The non-working code does not produce an error. It just fails to change the name.

rngNamedRanges.Cells(1,6) refers to a cell containing straight text.
rngNamedRanges.Cells(1,8) refers to a cell containing a CONCATENATE formula which creates the new range name based on several other pieces of info contained in other columns.

Answer 1

Renaming is always a pain. Try the following:

Sub Rename()

    StrOld = "MyRange1"
    StrNew = StrOld & "_Renamed"

    Range(StrOld).Name = StrNew
    With ThisWorkbook
        .Names(StrOld).Delete
    End With

End Sub

Looping is up to you. :) Let us know if this helps.