Unused Named Cells

**beetlejuice1976** · 06-14-2007, 05:49 PM

Hi

I have compiled a spreadsheet using a previous file I had created.

As the data compiled is vastly different than the last I have a huge drop down list within the 'Name Box' of previously named cells/ranges.

Is there a way to purge this list deleting redundant names without checking each ones integrity. (This list runs into hundreds, so will be a long process)

Will appreciate anyone's help

Regards

Steve

**mrice** · 06-15-2007, 02:29 PM

This macro should do it.

Sub Test()
For Each Name In ActiveWorkbook.Names
    Name.Delete
Next Name
End Sub

I'm not sure what you meant be redundant. If you mean contains no data then use..

Sub Test()
For Each Name In ActiveWorkbook.Names
    If Application.CountA(Range(Name.RefersToRange.Address)) = 0 Then
        Name.Delete
    End If
Next Name
End Sub

Unused Named Cells

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Unused Named Cells

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions