Is it possible to show a list of named cells that are used?

Morning everyone,

I have a spreadsheet which I've inherited from someone else and it has at least 100 named cells/ranges. While they are all valid (i.e. no #REF errors in the Name Manager) I can only really see that about 5 of them are being used in the calcs. I need to tidy up the spreadsheet so I want to delete the surplus names.

Is there a simple way to get excel to tell me if (and where) a named cell is used within the workbook or am I going to have to show formulas and search for each name seperately and delete it if I get no matches?

Thanks,

Alice

The macro below will list all of the unused names for you.

If you like I can modify it to automatically remove those names.

Sub UnusedNames()

Dim nName As Name
Dim rngFindName As Range
Dim sUnused As String

sUnused = ""

With ActiveWorkbook

For Each nName In .Names
  Set rngFindName = Cells.Find(nName.Name, LookIn:=xlFormulas, lookat:=xlPart)
  If rngFindName Is Nothing Then
    sUnused = sUnused & vbNewLine & nName.Name
  End If
Next nName

End With

If sUnused = "" Then
  sUnused = "All names are being used"
Else
  sUnused = "The following names are unused:" & sUnused
End If

MsgBox sUnused

End Sub

That's great, thank you.

I have just completed my first VBA training course so I will go through the code and see if I can get the macro to delete all the cells as well...watch this space, i'll come back if I get stuck (or if I work it out!)