Referring to a table by name or row/column ranges by name is handy in both sheet formulae, pivot tables and VBA. As a table is maintained, its number of rows (usually) changes. Here's a way to resize automatically, in this case the table is called Database and the top row is used for names:
This can be done EITHER with worksheet functions or VBA.
First Worksheet Function--
1. Display the Define Name window (Insert/Name/Define)
2. Enter an appropriate name (could be the columns heading) in the Upper textbox
3. Enter the following formula in the Refers To textbox (This formula assumes a single column list starting in Column A Row 2 with heading in Row 1 on Sheet1 AND no data below the list...
Sub ResizeTable() Application.DisplayAlerts = False With Cells(1, 1).CurrentRegion .CreateNames _ Top:=True, _ Left:=False, _ Bottom:=False, _ Right:=False .Name = "Database" End With Application.DisplayAlerts = True End Sub
This assumes that your table starts in A1.
2. call the procedure from the corresponding worksheet object, Worksheet_Change event; ie, every time you change a value on a sheet, this will execute. Optionally, you could use another event such as the Worksheet_Deactivate in order that it will not execute as often.