Resize a Table using VBA
 

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

Resize a Table using VBA
 

Example, code you tried, xlversion.??
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"OMER" wrote in message
...
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

Resize a Table using VBA
 

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



"OMER" wrote in message
...
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

Resize a Table using VBA
 

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?

"Don Guillett" wrote:

Example, code you tried, xlversion.??
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"OMER" wrote in message
...
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



.

Resize a Table using VBA
 

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

Resize a Table using VBA
 

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

"JLGWhiz" wrote:

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



"OMER" wrote in message
...
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




.

Resize a Table using VBA
 

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

"Herbert Seidenberg" wrote:

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

Resize a Table using VBA
 

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

Resize a Table using VBA
 

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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"OMER" wrote in message
...
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?

"Don Guillett" wrote:

Example, code you tried, xlversion.??
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"OMER" wrote in message
...
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



.

Resize a Table using VBA
 

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


"Herbert Seidenberg" wrote:

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