Automatically Expand a Named Range in Excel

I usually put a name to each data table created for referencing information in in Excel, as in a Named Range. If you add data to the bottom of the table, the Named Range isn't modified and any reference to it will fail to include the new information.

Here's a table of data I stuck on a worksheet called MyData and the range A2:E10 is named myFoodData.

Inserting a row inside this range will automatically expand the reference for the Named Range, but normally a user would add data to bottom of the table in the first empty row.

My solution is event based. I write a simple subroutine.

Sub ShiftRangeAndRename() Const n As String = "myFoodData" Dim rng As Range Set rng = Range(n).CurrentRegion Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count) rng.Name = n End Sub

Then reference it from the deactivate routine on the MyData worksheet.

Private Sub Worksheet_Deactivate() Call ShiftRangeAndRename End Sub

When a user goes to the MyData worksheet and updates data and returns to the main worksheet, the worksheet deactivate routine calls the routine to update the range reference and its associated name. This also works if they are deleting data, but that's not common in this type of situation.

Archived comments

Comments migrated from the previous version of the site. Adding new comments is disabled.

RichDecember 22, 2010 at 09:52 PM

instead of VBA, why not simply use this as your named range. Define the range 'myFoodData' as "=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))" no event macro needed

Gregoryexcelsemipro.comDecember 22, 2010 at 10:28 PM

The notorious OFFSET function is a VOLATILE function, which means it's updating when it doesn't have to be updating. I once knew a developer who had a very large file with lots of data and PivotTables galore and a great many OFFSET Functions. When a spreadsheet tab was selected, nothing could be done until the all the OFFSET functions updated. When you selected a PivotTable, nothing could be done until all the OFFSET functions updated. Even though, in both cases, there was nothing to update. No data had changed, yet there was always this infernally slow updating going on that essentially froze Excel for 5 to 10 seconds. That's why I'm not enamored of the OFFSET function. It's VOLATILE. It updates like the RAND function, all the time, and when it's not required. (And I'm biased.) However, in this particular file your suggestion would make sense. I'll have to give it a try. The only other problem I can see is that with Users, sometimes they do things that are unpredictable like making a note in column A that's 10 cells below the table, which effectively changes the range and would add 9 blank cells and one irrelevant data point to the range.

Enjoyed this guide?

Archived comments