RWayneH
asked on
Chart not expanding when a new date is added.
I have a defined chart area called "PltChartRange" and in a macro I insert a row to expand the chart range each time expecting that the chart will grow based on the insert. Is there a setting that the chart needs to know it has to expand?
Even if I manually insert a row in the chart range it should expand the range right?
I attached a sample file to reivew. Why does the chart not expand? Also it looks like the chart is expanding vertically as the rows are added, but not horizontally to add new rows?
Something is wrong.
Quick code to add a row at row 2 to expand does not show in the chart?
Even if I manually insert a row in the chart range it should expand the range right?
I attached a sample file to reivew. Why does the chart not expand? Also it looks like the chart is expanding vertically as the rows are added, but not horizontally to add new rows?
Something is wrong.
Quick code to add a row at row 2 to expand does not show in the chart?
Sub Macro4()
'
' Macro4 Macro
'
'
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A2").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("B2").Select
ActiveCell.FormulaR1C1 = "500"
Range("C2").Select
ActiveCell.FormulaR1C1 = "250"
Range("D2").Select
ActiveCell.FormulaR1C1 = "50%"
Range("D3").Select
End Sub
ChartThatAddsDates.xlsm
Just looked at the file and I suspect the graph has been setup thus:
Data selected rows 2 to 7
Series edited to add headers from row 1
This chart data range is row 1 and rows 2 to 7 rather than rows 1 to 7.
Macro inserts at row 2 pushing it down as suggested earlier.
Select graph and use Select Data option to select A1 to D13 and then remove series created for columns B & C.
Thanks
Rob
Data selected rows 2 to 7
Series edited to add headers from row 1
This chart data range is row 1 and rows 2 to 7 rather than rows 1 to 7.
Macro inserts at row 2 pushing it down as suggested earlier.
Select graph and use Select Data option to select A1 to D13 and then remove series created for columns B & C.
Thanks
Rob
ASKER
You are correct, I am just shifting the data down. The chart range is in the dropdown above cell A1 called "PltChartRange". Is there something else that needs to be done to the chart in order to grab the new stuff?
Inserting a row above the chart series range does not include that line.
Also inserting an entire row changes the size of the chart, which I don't think is intended.
Using Now as a formula for the date will change the value tomorrow, so not good.
I guess you want to insert cells from column A to F for a new days values.
Insert todays date in A3, and a formula to calculate the percentage in cell D3.
Fill values in B3 and C3 manually.
Here is an example, Press the button to run.
Have inserted a row with empty cells (row 2) as the start of the chart series.
So when inserting in row 3 it will be included in the chart series range.
Row 2 is hidden.
Macro inserts range A3:F3, but only when the date in A3 is before today, so you can not insert twice on the same day.
The formula in D3 calculates C3/B3 when possible, else the value NA (Not Available) to make the chart display correctly.
Conditional formatting in column D, so when value is NA, text colour is white, to make it invisible.
The code is this
Also inserting an entire row changes the size of the chart, which I don't think is intended.
Using Now as a formula for the date will change the value tomorrow, so not good.
I guess you want to insert cells from column A to F for a new days values.
Insert todays date in A3, and a formula to calculate the percentage in cell D3.
Fill values in B3 and C3 manually.
Here is an example, Press the button to run.
Have inserted a row with empty cells (row 2) as the start of the chart series.
So when inserting in row 3 it will be included in the chart series range.
Row 2 is hidden.
Macro inserts range A3:F3, but only when the date in A3 is before today, so you can not insert twice on the same day.
The formula in D3 calculates C3/B3 when possible, else the value NA (Not Available) to make the chart display correctly.
Conditional formatting in column D, so when value is NA, text colour is white, to make it invisible.
The code is this
Sub NewDateLine()
If Range("A3") < DateSerial(Year(Now), Month(Now), Day(Now)) Then
Range("A3:F3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A3") = DateSerial(Year(Now), Month(Now), Day(Now))
Range("D3").Formula = "=IF(AND(B3>0,C3>0),C3/B3,NA())"
Range("B3").Select
End If
End Sub
ChartThatAddsDates1.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As I mentioned earlier, I suspect the split range was caused by the way the graph was created in the first place; either the data was selected and then the headers added or the ranges were selected using Ctrl and mouse clicks rather than Shift and clicks.
Thanks
Rob
Thanks
Rob
ASKER
Ok I believe I understand now how this works, as a maintenance question and as the chart gets huge, how do I limit the data to a month or a number that fits on the chart nicely, I thought about adding a Average to column E at the bottom of the range add a formula the would include the new day in the average, then copy that up to the new row. If I average a named column range, will that expand now too?
As a simple fix, if you hide rows they won't appear on the chart.
Thanks
Rob H
Thanks
Rob H
ASKER
and to add the average? If I put the average in column E, and named that column MSC_Average. Could I use =AVERAGE(MSC_Average) and as the chart range increases so would the MSC_Average named range?
I usually will do a copy, special paste to remove any formulas at the end. Just want to be sure that I am thinking correctly on the approach to the chart.
I usually will do a copy, special paste to remove any formulas at the end. Just want to be sure that I am thinking correctly on the approach to the chart.
ASKER
Thanks for the help, understanding the chart, settings. -R-
Open in new window
However, that as you rightly point out does not add to the graph, are you expecting the new row to become a new series in the graph?Where does current graph range start? If the range starts at row 2, by inserting a row at row 2 you are just pushing the grah data range down one row. I assume row 1 is headers and is included in the data range.
Rob H