Automatically Expand a Named Range in Excel | Excel Semi-Pro

Automatically Expand a Named Range in Excel | Excel Semi-Pro

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.

Related posts:

1. Put an OFFSET Formula Inside a Named Range
2. Become the OFFSET Function and Tell a Short Story
3. Expand Menus in Excel 2003
4. Name and Select a Range with the Name Box in Excel
5. The VLOOKUP Function in Excel