Prev: reading and replacing just the first line of a txt or csv file XL2
Next: reset the value of a spin button
From: OMER on 7 Apr 2010 17:33
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


From: Don Guillett on 7 Apr 2010 18:18
Example, code you tried, xlversion.??
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"OMER" <OMER(a)discussions.microsoft.com> wrote in message
news:0F4C9975-00B7-4509-9AD0-CE7FEC46FC95(a)microsoft.com...
>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
>
>

From: JLGWhiz on 7 Apr 2010 18:44
I believe you would have to re-define your table.



"OMER" <OMER(a)discussions.microsoft.com> wrote in message
news:0F4C9975-00B7-4509-9AD0-CE7FEC46FC95(a)microsoft.com...
>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
>
>


From: OMER on 7 Apr 2010 19:41
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
> dguillett(a)gmail.com
> "OMER" <OMER(a)discussions.microsoft.com> wrote in message
> news:0F4C9975-00B7-4509-9AD0-CE7FEC46FC95(a)microsoft.com...
> >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
> >
> >
>
> .
>
From: Herbert Seidenberg on 7 Apr 2010 19:55
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
 |  Next  |  Last
Pages: 1 2
Prev: reading and replacing just the first line of a txt or csv file XL2
Next: reset the value of a spin button