Delete ALL named ranges

[ExcelRoy]

Hi,

would there be a code to delete all the named ranges in a workbook, but keep the cell referenced formulas so the calculations remain intact

Thanks

 

[WinteE]

This will do the trick:

Sub Test()

For Each nam In ActiveWorkbook.Names

    a = nam.Name
    b = Replace(nam.RefersToRange.Address, "$", "")
    
    For Each ws In ActiveWorkbook.Worksheets
    
        Cells.Replace What:="" & a & "", Replacement:="" & b & "", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    Next ws

    nam.Delete

Next nam

End Sub

Be careful, when a range is called 'then' it will also replace all words 'then' in typed text.

 

[ExcelRoy]

Pefect, thanks

 

[jbeaucaire]

Very nice method, thanks for that.

My followup question regards your Replace() command to remove the $ symbols from the addresss. Why did you choose do that? Since most named ranges are "absolute", converting to flat addresses I would expect to be converted to absolute range addresses? No?