Growing list of chart data

Solved/Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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?

14 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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(....."
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
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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.
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.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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 ?
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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.