Also, if the columns used for the data contain any other entries, COUNTA will return an incorrect value.I have been given a set of historic data that requires line charting for the given range (say 12 months).I’ll end with some recommendations of when each method should be used.
Thank you I have run into this same issue - not sure why, and when it happens the only way I have ever gotten the chart to force update is to change something in the chart definition itself, which can easily be done via VBA as in: There may be a better answer that gets to the bottom of the problem - but I thought this might help. Formula = temp Next sc Next co This is an absurd bug that is severely hampering my work with Excel. It happens to be a problem of refreshing the generated charts. This works for me and also handles similar issues with charts as well as chart objects. And by the way, these are chart objects, not chart sheets. I was dynamically building a querydef, setting that as the rowsource on my report and then trying to loop through each series and set the properties of each series.
It cuts the root cause of the problem (I assume): the series data is getting cached in the chart. Also, I'm not really sure why this works; I suppose when Wrap Text changes one property of the data range it makes the graph update, although I have no documentation on this. Programmatically, I added this after generating charts : Ok I have a solution, really.... I did notice, however, that every time I made a change in the code my dashboard worked. The setting of Enable Events and xl Manual keeps infinite recalc loops and other event problems from occurring. I tried just about everything on this thread with no luck.
By forcing the chart to re-evaluate the series, we are clearing the cache. 'Refresh all charts For Each mysheet In Active Workbook. I found that the problem with my charts not updating first occurred shortly after I had hidden some data columns feeding the chart, and checked "show data hidden in rows and columns" in the Chart's "Select Data Source" msg box). Thus: Solution: I faced the same problem with my work last week when I added some more calculation to my sheet. I had this problem and found that it was caused by having two excel applications running at the same time. I finally realized that the charts that I was copying and pasting were linked to the source data, and that is why they were all showing the same results.
After you perform these steps, when you add data to columns B and C, the chart updates automatically to show the new data (see on the top of this screen).
To use this technique for your own data, make sure that the first argument for the OFFSET function refers to the first data point, and that the argument for COUNTA refers to the entire column of data.