PDA

View Full Version : Solved: VBE to change Line Chart Height based on number of Rows



JimS
01-23-2009, 11:03 AM
I have a Line Chart that uses Names Ranges as the Source(s) because the number of entries (rows) can vary greatly in my data set (anywhere from 10 to 100 entries/rows).

How can I have the Line Chart expand down as more entries are entered?

The Chart will show the new entries but you cannot read the Labels beacuse they are to small and appear almost on top of each other becasue the Chart does not "physically" expand down automatically.

Is there a way to have a chart's physical "size" change automatically to allow for new entries so that the Labels are readable?

ie: overall chart height = .x for each row, where x is a constant height setting?

Thanks for any and all help...

Jim

JimS
08-18-2009, 09:57 AM
Anyone have any ideas?

mdmackillop
08-18-2009, 11:31 AM
A simple example saves us from having to create a workbook trying to replicate your problem.

JimS
08-18-2009, 05:57 PM
Here's an example of the data.

The number of rows can vary.

tpoynton
08-18-2009, 06:38 PM
here's one idea, but I'd like to see if a method exists that doesnt need to refer to the data sheet...


Public Sub expandChartToRows()
With ActiveSheet.ChartObjects(1)
.Height = (ThisWorkbook.Sheets("Data1").UsedRange.Rows.Count + 10) * 14
End With
End Sub

JimS
08-19-2009, 06:23 AM
This will work - Thank you very much....