×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here
Home > Forums > MIS/IT > Office Suites > Microsoft: Office > FAQs

Microsoft: Office FAQ

Best of Excel

How can I rename a table as it changes size by SkipVought
faq68-1331
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()
    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.

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

My Archive
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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close