Excel Named Ranges Change Name via VBA

[ramridge]

Guys -
I have inherited a spreadsheet with hundreds of redundant named ranges. I want to delete them all via VBA. I did the usual by using nm.Delete where nm is the variable Name object. It works fine except that there are names which have blanks embedded, eg. "Direction_20_1_ 2" or "South 23_1_34". The VBA code produces an exception error because of the blanks. How the names got in there in the first place beats me. Is there a way within VBA to change the name? If not, can anyone please think of another solution? Deleting them (there are around a hundred) from Excel's Name Manager is possible but I will have to do this exercise every time I get a fresh copy - and that is outside my control.
Many thanks, ramridge

 

[VoG]

Try this for the bad names

Sub DeleteBadNames()
Dim nm As Name
Dim vTest As Variant
For Each nm In ActiveWorkbook.Names
    vTest = Empty
    On Error Resume Next
    vTest = Application.Evaluate(nm.RefersTo)
    On Error GoTo 0
    If TypeName(vTest) = "Error" Then nm.Delete
Next nm
End Sub

 

[ramridge]

Hi VoG -
Sorry it didn't work.
The bad name is eg. "Datasets_names_40_1 1". When it gets to your code in the last but one line " ...then nm.Delete", it is trying to delete a name with an embedded blank and throws an error.
Regards. ramridge

 

[wigi]

Hello

I've seen very peculiar cases in the past, with names that could not be deleted.
I stopped searching for a solution and deleted manually.

Do you have any control over the files that are being given to you?
The problem should be solved over there...

 

[ramridge]

Afraid not. I will receive these from an external source on a regular basis. To delete them manually (around 100 of them) every time will be a pain in the proverbial. Would prefer to have a VBA which would get rid of them automatically at the start, before I start to use the xls.
Regards. ramridge

 

[trunten]

If it's just one worksheet can you just copy and past to a new workbook leaving all the redundant names behind?

 

[ramridge]

That's exactly how I found out that I had a problem. Cannot copy to another workbook. Keeps asking me if I want to change the name for everyone of the hundred or so bad names.

 

[trunten]

Ouch.
I haven't tested this (despite the name) but give it a whirl. If it doesn't work hopefully you get my logic.

Sub test()
    Dim n As Name
    Dim e, d
    Dim thisWB As Workbook, newWB As Workbook
    Dim i As Long
    
    Set d = CreateObject("Scripting.Dictionary")
    Set thisWB = ActiveWorkbook
    For Each n In thisWB.Names
        e = Empty
        On Error Resume Next
        e = Application.Evaluate(n.RefersTo)
        On Error GoTo 0
        If Not TypeName(e) = "Error" Then
            d.Item(n.Name) = n.RefersTo
        End If
    Next n
    Application.screenupdating = false
    Set newWB = Workbooks.Add
    While newWB.Sheets.Count < thisWB.Sheets.Count
        newWB.Sheets.Add
    Wend
    For i = 1 To thisWB.Sheets.Count
        newWB.Sheets(i).Name = thisWB.Sheets(i).Name
        With thisWB.Sheets(i).UsedRange
            newWB.Sheets(i).Range(.Address).Value = .Value
            .Copy
            newWB.Sheets(i).Range("A1").PasteSpecial xlPasteFormats
        End With
    Next i
    For Each e In d.keys
        newWB.Names.Add e, d(e)
    Next e
    Application.screenupdating = True
End Sub

 

[ramridge]

Brilliant. Worked first time flawlessly.
Trunten, you are the man.