Macro for removing #N/A data from graph

**larsft** · 03-17-2011, 06:08 AM

Hi!

I have a problem. I won't to make a chart over som data, but the amount of data is varies from 1 to 100 cells. I have made an if-calculation that returns #N/A for all cells that does'nt contain any valid data.

My problem is that the chart still incluce the cells with #N/A in it, and I can't get the macro for deleting this values to work.

I have tried this macro, but I get an 80004005 run-time error. VB tells me that the error lies in line 8.

Sub DeleteNADataLabels()
Dim s As Series, p As Point
If ActiveChart Is Nothing Then
MsgBox "No chart selected"
Exit Sub
End If
For Each s In ActiveChart.SeriesCollection
If s.DataLabels(7).Type = xlDataLabelsShowValue Then
s.DataLabels.Delete
ActiveChart.ApplyDataLabels xlDataLabelsShowValue
For Each p In s.Points
If p.DataLabel.Text = "#N/A" Then p.DataLabel.Delete
Next
End If
Next
End Sub

Hope somebody could help me. I really need to get this up and going. unfortunately my expirience in programing macros is near zero.

Best regards

Lars

**Andy Pope** · 03-17-2011, 06:27 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

What chart type are you using?
It would help if you posted example workbook.

**larsft** · 03-17-2011, 09:18 AM

Hi again! Sorry about rule 3. I'll do it correct next time!

It is a line chart. It is supposed to show the future value of an investment done today at a given rate of return for a specified amaount of years into the future.Thou the number of years is a variable, and change from 1-100.

I can post the example workbook tomorrow, but no I'm not at my computer. At the moment I just need somthing to get started on. A macro that only choose cell with a number inside, and exclude cells with #N/A, and then make a line chart based on the selected values.

Thanks for your time!

Lars

**larsft** · 03-17-2011, 10:36 AM

Here is the xlsx file:

**Andy Pope** · 03-17-2011, 10:43 AM

That does not require any code.
Search for dynamic charts and see how to use named ranges to plot data.

Why do rows 13:102 have anything in them anyway?

**larsft** · 03-17-2011, 11:33 AM

Ok, I check it out!
the rest of the rows will contain data if number of years changes to a bigger number than 10. If I am going to show a 100 year future cash flow, all the cells will contain numbers.

That was my issue, how to make a chart that only pick the cells with numbers, but could make a graph for all the cells if number of years was set to 100.

Thanks a lot for your help. I'll check out thoes dynamic charts.

Lars