×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Microsoft: Office FAQ

## Best of Excel

How can I rename a table as it changes size by SkipVought
Posted: 6 Dec 01 (Edited 26 Mar 07)

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()
With Cells(1, 1).CurrentRegion
.CreateNames _
Top:=True, _
Left:=False, _
Bottom:=False, _
Right:=False
.Name = "Database"
End With
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.

Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!