Range Names and Macros

Range names rule

When you record a macro that refers to a particular cell or range on a particular sheet in Excel the range reference is hard coded into VBA (macro) code. Unfortunately this means if rows or columns are inserted or deleted in the reference range the code is not updated. There is an easy way to get around this.

Delete range names with #REF errors

A macro to the rescue

Here is an example of a simple macro that solves a problem in Excel 2003 and earlier versions.

Range names can be corrupted if a cell that they refer to gets deleted. This doesn’t mean that the cell value gets deleted, but the cell itself is removed from the sheet.