Make 0 Value Cells = Blank / Null

I've seen a thousand of these threads but none can solve my problem.

I have spreadsheets that I am referencing in my master spreadsheet, so cells contain formulas like: =C:/Documents/.... you get the idea. So my problem is, the blank cells on the supporting spreadsheets are appearing as 0 on the master spreadsheet. However, I am trying to make charts, and I want there to be no point for anything with a blank value, but instead I'm getting graphs with zero values instead of not showing up. I feel like there must be a vba code to solve this problem. I tried:

Please Login or Register  to view this content.

But I'm getting an error message on rng.Value = 0 so, not sure what is wrong with it. I'm using excel 2010. I can't just hide the 0 values, I need them to be deleted/replaced completely.

Thanks for your help!

First, if you want the graphs to ignore your data, you need to use NA().

Second, I didn't see anything wrong with your code, so I typed it in and it worked. Here is my code (over a smaller range and outputting NA's). Let me know if this works on your system. What error message are you getting?

Please Login or Register  to view this content.

Pauley

This was successful! Thank you so much. I didn't get the error again so I don't know what it was. Thank you so much for your help!

Originally Posted by Pauleyb

First, if you want the graphs to ignore your data, you need to use NA().

Second, I didn't see anything wrong with your code, so I typed it in and it worked. Here is my code (over a smaller range and outputting NA's). Let me know if this works on your system. What error message are you getting?

Please Login or Register  to view this content.

Pauley

I spoke too soon. I got the error again. Run time Error '13' Type mismatch. It's actually not working..... I have zeros being hidden but the values are still there and even this code does not make them N/A.

You have some bad data somewhere. A text '0' should still work with this code and a blank should also be converted to NA#. Note, however, that an NA# will cause the error you describe. This should help you find the problematic cell(s).

Please Login or Register  to view this content.

Pauley

I think I'm working with number values of 0, not text, not sure if that matters. This is changing blank cells to N/A, not just 0 cells to N/A. It's been filling in all the blank spaces in my sheet with N/A lol The program runs till it gets to a cell that already has an error (#ref) and then stops working. If you know a way to bypass the #ref cells and continue that'd be great, or a way to only change the 0 values, not the blank ones I'd really really appreciate it. Thank you so much for helping me, I'm really quite useless when it comes to VBA

Okay, I would think for charting you would want the blank spaces to be N/A, too. As your first post states, the blank cells are showing up as 0's and causing your chart to not format correctly. This code will ignore the blank cells and only change the 0 values. It will also ignore the #ref cells, but realize that will also affect your chart.

Please Login or Register  to view this content.

Pauley

Originally Posted by Pauleyb

Okay, I would think for charting you would want the blank spaces to be N/A, too. As your first post states, the blank cells are showing up as 0's and causing your chart to not format correctly. This code will ignore the blank cells and only change the 0 values. It will also ignore the #ref cells, but realize that will also affect your chart.

Please Login or Register  to view this content.

Pauley

PERFECT solution. Thank you SO much for your help, I really can't thank you enough. I truly appreciate it, this was incredibly helpful