Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How can I rename a table as it changes size

Best of Excel

How can I rename a table as it changes size

by  SkipVought  Posted    (Edited  )
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.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top