Macro to ignore #N/A error for charting

[Philb1]

Hello Everyone.. I've managed to create a macro that will select a range of cells & create a chart. The chart's a bit wonky, but that doesn't matter for now. As excel needs to have #N/A in a cell, rather than "" for it to be able to create a chart, I need find a way of making the selection process ignore the errors in the cells under the lowest cell with data in it. When the chart is created as it is now, it has spaces where the #N/A error is at the end of the chart. If those bottom error cells were ignored & the data selected, the charts last bar would be at the end of the chart.
I've attached a sample file of what I'm trying to do
Any ideas please?
Thanks

[Andy Pope]

you could try

Please Login or Register  to view this content.

Or resize the ranges once you determine the longest using the xlDown approach rather than xlUp.

Please Login or Register  to view this content.

[Philb1]

Hi Andy, Thanks for the reply.. I tried your 2nd suggestion but it didn't make any difference. It still selected every cell, regardless of whether it had a number or #N/A in it. I didn't try the 1st as I thought current region only selected cells immediately next to a selected cell. I found another macro that selected every cell on a sheet with a number in it. I've changed it to only select the columns I want & added the chart create to it. Apart from it being a bit wonky, it creates the chart almost as I would like. The problem is it has a series for every selected cell instead of for every selected column, resulting in multiple colours for the bars. The attached file creates 13 series when the chart button is pressed. Any ideas on fixing that?
The code is below. I haven't tidied it up as far as referring to the wrong things yet, although it does refer to the right columns & make a chart
Cheers

Please Login or Register  to view this content.

[Andy Pope]

Your original example had a gap between the block of data and a final row. I assumed this final row was what you meant when saying, "If those bottom error cells were ignored".

Does using autofilter and not showing hidden cells give you the chart you are after?

[Philb1]

Sorry about that. I knew what I meant but didn't convey it very well.
I found a formula that does almost what I want. It's one DonkeyOte posted about 18 months ago. It ignores all the cells with formulas sitting there unused. I can use that formula to find the last date entered in a column, but the problem I'm having now is I want to use result as part of a reference to select the columns with the data for the chart. There's 3 sets of columns altogether, but only 1 set per chart macro. I'm trying to get it to select T5:V24, but it gives the Method range of global object failed error. I can't figure it out. T5 will always be the first cell in the range. T, U or V could be the last depending on the result of the last day.
Any ideas on that one? Hopefully the offending line should be highlighted red
Thanks

Please Login or Register  to view this content.

[Andy Pope]

Please Login or Register  to view this content.

[Philb1]

Thanks Andy.. I had tried almost exactly the same solution & the reason it didn't work was I had a quote mark in the wrong place. I tried a million things lol.
I tried the filter you suggested, but it deleted the rows with #N/A error. That wasn't what I needed. What I was/am trying to do is have a macro that will create a chart depending on the results from a month. Enter the 1st of a month in a cell, then index/match formulas refer to another sheet & bring up the results in different columns. There's 3 sets of columns for different types of result. With this macro it's able to select all the cells where the results are & create a chart, all at the press of a button. 3 buttons, 3 different charts. I knew it was possible
I've attached the macro file for anyone who is trying the same thing
Cheers