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...
Code:
=OFFSET
(
INDIRECT("Sheet1!$A$2"),
0,
0,
CountA(Sheet1!$A:$A)-1,
1
)
4. If this is a TABLE, for instance, one to use as PivotTable Source Data, same assumptions AND nothing in ROW 1 to the right of the table...
Code:
=OFFSET
(
INDIRECT("Sheet1!$A$1"),
0,
0,
CountA(Sheet1!$A:$A),
CountA(Sheet1!$1:$1)
)
Next VBA--
1. Write this procedure in a general module
Code:
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.
