Growing list of chart dataSolved/Closed

Question

Hello,

Column K contains dates, column L contains amounts. I want to create a chart out of this data.
The issue is that the list of data is growing.
Now I was thinking to create a name for the dynamic range and use that name to create my chart.

I recorded a macro to get started:
Names.Add Name:="GrafiekData", RefersTo:="=Feestdagen data!K3:L20"
Now I would like to change L20 to something like L1040.End(XlUp)

Hopefully I made clear what I am aiming for.

Best regards,
Trowa

PS. I couldn't find the preview option anymore. Is it lost in the update?

rizvisa1 · Answer

Trowa how about some thing like this


maxrows = cells(rows.count, "L").end(xlup).row

Names.Add Name:="GrafiekData", RefersTo:="=Feestdagen data!K3:L" & maxrows 

There is another way too, where you dont have to use name.  See this for that
https://ccm.net/forum/affich-281219-changing-cell-ranges-in-all-series-of-a-graph

basic idea is to use 
ActiveChart.SeriesCollection(1).Formula = "=Series(....."

Trowa · Answer

Hi Rizvisa,
The second solution is a bit to complicated for me and it looked to me that Sisa has a couple of fixed ranges, where I have one growing range.

The first solution needs a little adjustment for it to work.:
Since the sheet "Feestdagen data" already contains a lot of data, I have compiled the chart data on the chart sheet (called "Grafiek"), so that it doesn't interfere with the row count.
Data is now in range A42:B??.
The problem is that the chart data is being pulled from a different sheet via formula, thus not being an empty cell: 
Cell A42: =IF('Voltooide opdrachten'!M2=0;"";'Voltooide opdrachten'!M2)
Cell B42: =IF('Voltooide opdrachten'!M2=0;"";'Voltooide opdrachten'!M2)
These formula's are dragged down untill row 1100.

The created named has a range of A42:B1100.
How do I make the code to look for empty values instead of empty cells?

Thanks in advance,
Trowa

rizvisa1 · Answer

would it be possible to post the book ?

I am sorry, I did not follow your "empty value", vs "empty cells. If by empty value you meant "", I am not sure is there is a way where an empty cell and cell with value "" can be differentiated. Perhaps you can add " " (a space)

For the 2nd sol, just in case it helps, each time his start and end row would change too . In his case, the graph was always there, only data was replaced every time. So if you look at your graph, you would find that it says

=SERIES( label, x data range, y data range)

, So idea was to change this series formula by macro, This can be done by 

ActiveChart.SeriesCollection(1).Formula = "=Series(.....)"

Trowa · Answer

Let me explain what I mean by "empty value", vs "empty cells":
When I run the macro:
Sub GrafiekBereik()
maxrows = Cells(Rows.Count, "B").End(xlUp).Row
Names.Add Name:="GrafiekData", RefersTo:="=Grafiek!A42:B" & maxrows
End Sub
The name "GrafiekData" is assigned to the range A42:B1100, because formulas fill those cells.
I want it to be assigned to the range A42:B46, because those cells contain the values I want to build a graph of.
So apparently the macro looks for cells which aren't empty and gives that range a name.
But I want the macro to look for cells with values and give that range a name.

Let me post my workbook and explain what I would like to see happen:
Sheet "Alle opdrachten" displays incoming delivery orders.
When the order has been delivered the line turns green.
At the moment I have put 5 green lines of orders to test with.
When the user clicks on the button "Voltooide opdrachten afvoeren" the green lines will be moved to the sheet "Voltooide opdrachten" to display a summary of what has been finished.
On the sheet "Voltooide opdrachten":
Column E holds the amounts which has been delivered. 
Column M holds the date which the order has been finished on.
Out of these 2 columns I would like to create a graph.
I thought to create a graph the columns had to be next to each other.
So on sheet "Grafiek" starting on line 40 (so I can put the graph above it) I use formulas to pull the data from the sheet "Voltooide opdrachten".
Now I would like to create a standard column graph:
X axis shows dates.
Y axis shows amounts.

Note: I didn't create a graph yet, since I don't have a named range yet.

Here's the link:
https://authentification.site/files/21625935/Copy_of_PostPlanning.xls

I'm curious to see if you would go for your first or second solution.

Thanks in advance and kind regards,
Trowa

rizvisa1 · Answer

The column do not have to be next to each other. They can be separate too When it comes to creating graph, I am novice. I will try to see if I can create one today. Stay tuned.

Trowa · Answer

Hi Rizvisa,

I don't want to sound impatient or disrespectfull, but are you still working on this?
Please take the time you need, just want to know if I can still expect an answer.

rizvisa1 · Answer

Sorry man. It just slipped my mind. I will look into it today. My graph skills are very weak so it just might take a bit longer. Hopefully you are pro at graphs, so if you could just create a graph and post link, it will save me a bunch of time

Thanks for the reminder

Trowa · Answer

I can imagine something might slip your mind since you're helping out so much people (props to you for doing so! [the helping part, not the slipping mind part :) ] ).  
I'm just glad you're still helping me since graphs are not your strongpoint (according to yourself). 

As you requested I've created a graph at the top of sheet "Grafiek". I used some example data which I put in the range D40:E47 of the same sheet "Grafiek". 

Here is the link: 
https://authentification.site/files/21738790/Copy_of_PostPlanning.xls 

Kind regards, 
Trowa

rizvisa1 · Answer

Trowa

Question for you

1. Will the graph always remain in the workbook and only each time just the data would change ?
2. You have data in Grafiek. From what I understood from you, this is the same data (all rows) that can be found in column E and M on sheet Voltooide opdrachten. Is there a reason that you cannot use the data on Voltooide opdrachten directly ?

Trowa · Answer

1. Yes, the graph will remain in the workbook and only each time the data range will change.
2. Correct and there is no specific reason other then that I thought that it would be easier to put both columns next to each other in order to create the graph.

Kind regards,
Trowa

rizvisa1 · Answer

Try this   (assuming graph is already there)

Sub RefreshGraph()   
Dim lMaxRows As Long   

    Sheets("Voltooide opdrachten").Select   
       
    lMaxRows = Cells(Rows.Count, "E").End(xlUp).Row   
       
    Sheets("Grafiek").Select   
    DoEvents   
       
    ActiveSheet.ChartObjects("Chart 1").Select   
    DoEvents   
       
    ActiveChart.SeriesCollection(1).Formula = "=SERIES(""Data""" _   
                                            & ",'Voltooide opdrachten'!$M$2:$M$" & lMaxRows _   
                                            & ",'Voltooide opdrachten'!$E$2:$E$" & lMaxRows _   
                                            & ",1)"   
       
       
End Sub

This is relying on data from Voltooide opdrachten. How ever we can use the data from Grafiek. Due to formula, we cannot use End(xlUp).Row. For that either we loop thru each row to find out if the value is blank or not   



In that case you can use this to find out the max rows   

Function MaxUsedRows(Sheet As String, column As String)  as long  
'sample call       lRows = MaxUsedRows("Grafiek","A")   
Dim lRows As Long   
Dim myRow As Long   

    Sheets(Sheet).Select   
       
    lRows = Cells(Rows.Count, column).End(xlUp).Row   
       
    For myRow = lRows To 1 Step -1   
        If (Cells(myRow, column) <> "") Then Exit For   
       
    Next   

    If myRow = 0 Then myRow = 1   
       
    MaxUsedRows = myRow   
End Function


Other alternative would be that you use copy and pastespecial as values to eliminate formula  and then you can use End(xlUp).Row to find the max rows 

Personally I think either use of Voltooide opdrachten data directly or use of MaxUsedRows function will be better.  Of course if you do use MaxUsedRows  function, make sure that you change the code to reflect that you would be using data from Grafiek instead of Voltooide opdrachten

rizvisa1 · Answer

Also just one more thing,
I saw that you have code like

Range("A4:M65536").Select

I am presuming that 65536 is the max row in sheets. If last possible row is needed, it would be safer and more portable to use rows.count

So 
Range("A4:M" & rows.count).Select

I do understand that 65536 is a big number, and in most cases we dont get to use all these rows, I just thought to give the suggestions as I myself used to do it in this very manner till I got to use 2007 and then all off sudden my last row was no longer 65536

Trowa · Answer

Thanks Rizvisa, using your RefreshGraph code does exactly what I want.

Also thanks for explaining  the use of rows.count.

Guess you can't say you are a graph novice anymore :) .

Kind regards,
Trowa

rizvisa1 · Answer

You are very welcome Trowa. Glad it worked out fine. Sorry it took two weeks.

Nope I am still a dufus when it comes to creating graph. Once you gave me the graph, then I found myself on familiar ground. It sound so stupid but creating graphs is my weakest spot in excel.

Growing list of chart data

14 responses

Similar discussions

Subscribe To Our Newsletter!