Resize a Table using VBA

[OMER]

I have a named table (not a range) that I need to resize depending on the
number of cols and rows from another worksheet. I tried the approach used to
resize ranges but it doesn't work for Tables. I can resize it using the
Ribbon-Table Tools, but these commands don't get recorded in the Macro
Recorder.

Is there code to accomplish this dinamycally inside a macro?

Regards,
OMER

 

[Don Guillett]

Example, code you tried, xlversion.??

 

[JLGWhiz]

I believe you would have to re-define your table.

 

[OMER]

Don,
I'm using Excel 2007.
This is the code (taken from another post on this same site)

Private Sub ResizeDashboardTable()
On Error Resume Next
Dim rng As Range
Dim sht As Worksheet

Set rng = ActiveCell
Set sht = ActiveSheet
Sheets("Dashboard").Select 'goto the sheet
Range("A1").Select 'goto the start point
Range(Range("A1"), Range("A1").Offset(0, 0).End(xlDown).Offset(0,
25)).Select 'select the range to resize
ActiveWorkbook.Names.Add Name:="Dashboard_Data_Table", _
RefersToR1C1:=Selection 'Resize the range
Range("A1").Select 'clear the selection
End Sub

After execution, the table size is the same as it was before, no change.

I tried this and othe similar alternatives. They work for Ranges, not for
tables.
Any ideas?

 

[Herbert Seidenberg]

Excel 2007 Table
Resize macro.
http://www.mediafire.com/file/ozuwywyztez/04_07_10a.xlsm
http://www.mediafire.com/file/dnwnzqziyvk/04_07_10a.pdf

 

[OMER]

What do you mean by re-define? Delete and create again?

 

[OMER]

Thank You Herbert.
Cannot access the files at mediafire.com

 

[Herbert Seidenberg]

Alternate download site:
http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_07_10a.xlsm
http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_07_10a.pdf

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[OMER]

Thank You so much Herbert. Exactly what I needed.
Selecting it as an Object (not as range) is the key. It worked smoothly.
Regards,
OMER