I recently posted about Creating Dynamic Names in VBA where created a bunch of names that would expand with the data provided. I created names that look like
I like Tushar’s idea so much that I deleted all my names and redid them using Offset (except column A of course). Tushar commented that I should set up column A like I posted, but then use Offset for all the other columns. “Brilliant”, I thought. If I ever need to change how ranges are defined, I only have to change one name. A few days later, the end user decided they didn’t need one of the columns and deleted it. The problem was that it didn’t return an error, it just returned the wrong results. The offset numbers are constant so deleting a column caused all of the later names to be off by one.
I “solved” it by restoring the column and hiding it.