CODEWITHSUNDEEP
vbaexcel
David Tucker
David Tucker

Reputation: 21

Deleting Named Ranges

I am running a macro that takes a sheet in the active workbook and copies it to a new workbook, and then attempts to delete the named ranges before saving. All iterations of deleting the named ranges that I have attempted have failed with the same error:

Delete named ranges error

Here is the code that I have tried, which is the most commonly suggested code I could find:

Dim nName As Name
For Each nName In Application.ActiveWorkbook.Names
    nName.Delete
Next

Upvotes: 2

Views: 843

Answers (1)

user4039065
user4039065

Reputation:

Too long for a comment; I will delete if this does not resolve the situation.

When you created the defined names in the workbook, you may have unintentionally used a reserved word or duplicated the name of a shape or other object. The following may help identify the defined name by its index, not the object itself. 

do while cbool(activeWorkbook.names.count)
    activeWorkbook.Names(1).Delete
loop

I believe that both the .Count and index of the Workbook.Names collection are 1-based.

Upvotes: 3

Related Questions