How to Delete Named Ranges

If you want to keep your workbooks clean, it’s useful to be able to delete unwanted Named Ranges.

In this 10 minute video we cover:

• Deleting Named Ranges using the .Delete method
• Deleting all Named Ranges in a Workbook using a For Each loop
• Deleting “Dead” Named Ranges that have value = #REF! errors due to non-existent references (because either the worksheet was deleted or rows/columns were deleted)
• Detailed look at the VBA function “InStr” to find #REF! in Named Ranges

Note on Names that can be used

Named Ranges in Excel can contain letters, numbers, and some special characters.

Here are some rules for Named Ranges:

• The first character must be a letter or an underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters.
• Names cannot be the same as a cell reference, such as Z$100 or R1C1.
• Spaces are not allowed. Underscore characters and periods may be used as word separators for example “First.Quarter” or “Sales_Tax”
• A name can contain up to 255 characters.
• Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. For example, if you have created the name Sales and then create another name called SALES in the same workbook, the second name will replace the first one.

Write your comments and feedback below

• If you have questions or comments about this lesson, write it in the section below.
• Alternatively you can email the instructor Victor at [email protected]