How can I find unused range names in a workbook?

[UongSaki]

I have a large workbook with multiple worksheets and numerous range names used in calculation. over time and with all the changes I made, I've last track of which range names are still is in use. How can I find the unused ones and delete them from the workbook?

Thank you,

Uong Saki

 

[SkipVought]

Hi,

Run Sub1

Review the list, removing any range name row that you want to keep.

Run Sub2

Sub Sub1()
    Dim r As Long, n As Object
    Sheets.Add
    r = 1
    For Each n In Names
      If Right(n.RefersTo, 5) = "#REF!" Then
        n.Delete
      Else
        If Application.CountA(Range(n.Name)) = 0 Then
          Cells(r, "A").Value = n.Name
          Cells(r, "B").Value = Right(n.RefersTo, Len(n.RefersTo) - 1)
          r = r + 1
        End If
      End If
    Next

End Sub
Sub Sub2()
    Dim r As Range, rng As Range
    If [A1].CurrentRegion.Rows.Count = 1 Then
        Set rng = [A1]
    Else
        Set rng = Range([A1], [A1].End(xlDown))
    End If
    For Each r In rng
        Names(r.Value).Delete
    Next
    Activesheet.Delete
End Sub

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[UongSaki]

Hi Skip,

Thank you for your help. The code you wrote is foreign to me since I’m not a programmer. Here are the error Excel returns:

For Sub1

Run time error 1004
Method ‘Range’ of object ‘_Global’ failed

For Sub2

Run time error 9
Subscript out of range

Thank you for you help!

Saki

 

[SkipVought]

1. Copy the code into a new module -- NOT A SHEET OBJECT. (Insert/Module)

2. run Sub1

3. Review the list, removing any range name row that you want to keep.

4. Select the sheet containing the list.

5. run Sub2.

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}