Business Tools Blog

A helpful tip from Zayo’s CFO, Ken desGarennes

This Excel Macro creates a list (first create a blank worksheet) of all of the named ranges in a workbook (both hidden and unhidden).

Sub ListAllNames()
Row = 1
For Each n In ActiveWorkbook.Names
Cells(Row, 1) = n.Name
Cells(Row, 2) = ” ” & n.RefersTo
Row = Row + 1
Next n
End Sub

This Excel Macro unhides the hidden named ranges.

Sub Adjust_Names()

Dim nme As Name

For Each nme In ActiveWorkbook.Names
nme.Visible = True
Next nme

End Sub

Unfortunately all of the macros I tried to use to delete the invalid named ranges did not work – which means you have to do it manually.