#### liquidmettle

##### New Member

- Joined
- May 23, 2013

- Messages
- 48

Alright, I'm going to explain my situation in length so that its clear what I wish to accomplish. One quick point: If Excel 2013 can make graphs that skip nominally blank cells (they'll have a formula that returns an error value or 0), then the rest of my post is moot. I've never used Excel 2010 let alone 2013, so I am unaware of whether this problem is solved or not. If someone knows conclusively, that's a good enough answer because I'll just upgrade. Otherwise I'm stuck going to macros, the thing I was trying to avoid.

**The short of the matter**is I am trying to create a dynamic chart (Bar Graph most preferable) that will update its values as they are entered in a different Sheet of the same workbook. However the range is variable (to a certain limit) as data may be added below the data at present, and these new entries need to be counted too.

To my dismay I've discovered Excel 2007 does not filter out N/A's, zeroes or errors from its graphs, so I end up getting all my data crowded at one end of the graph with the "blank" cells filling the rest.

**My attempts at creative workarounds:**My workbook is set up with quite a few sheets. Lets say Sheet4's data is being pulled into sheet "J-1." Two columns of data (Columns C and D in J-1) are being copied by a simple formula: this cell in J-1 equals that cell in Sheet4. Column C contains text entries. Column D contains monetary values.

In Sheet J-1, I've used the formula =COUNTIF($C$4:C4,C4) set in column B to give a sequential count next to all text values in column C.

In the next column over, column E, I've used the formula =IF(B4=1,C4,""). This only returns unique text values and skips the duplicates by leaving those cells blank in the column.

Column F then uses =IFERROR(INDEX(Blanks1,SMALL((IF(LEN(Blanks1),ROW(INDIRECT("1:"&ROWS(Blanks1))))),ROW(A1)),1),"N/A") to get rid of all the blanks and return an unbroken column of data.

Column G then uses =IF(F4="N/A","N/A",(SUMIF(C3:C67,F4,D3:D67))) to sum the values of column D for the duplicate entries that have now been removed.

So what I get in Column F and G is two columns of values that will continually update based on what data is entered into Sheet4. I've tried using Cols F+G as my range for the chart but the chart will not skip the ****able blank or error cells when plotting.

1. I know the difference between Na() to return "#N/A", and the "N/A" I've used for cosmetic reasons. I've tried NA(), 0, and "" to produce a null cell in one form or another, hoping the graph would not plot these bars. But alas, it was not to be.

__1. Ideally the macro will be automatic without requiring me to press a button or tell the macro to run via the developer ribbon.__

**What I need in a macro:**

2. Will either: hide all "N/A" (or suitable replacement) values by hiding the row they are in (this is acceptable) or otherwise making it so a graph will not count these as data to be plotted.

3. Will unhide these rows as soon as there are no more "N/A's", i.e. the data has been updated and the range of viable data increased.

4. Depending on feasibility I am willing to concede to having the macro slaved to a button that says Update that can update the graph by performing the functions above functions.

5. The macro will need to be able to apply to different ranges of cells. While all condensed data (Like Cols F and G mentioned above) will be in sheet J-1, the different sheets J-1 pulls these data sets from will need to each have their own dynamic graph. Basically the macro just needs to make sure the graph for each sheet skips the n/a etc.

**What I will also need:**1. I will need to know how to insert the macro properly. Let's call the Macro HideRows.

2. Do I just copy/paste any provided answers into the VBA editor? What then? Hit the X on the VBA window?

It may seem like I have some knowledge of this process (or not), but thats only because I've spent more time than I care to think about trying to work around this asinine problem of a bar-graph (or any graph other than a line) not being able to skip null values or "blank" cells.

I thank you in advance for getting to the end of this lengthy post, for considering it, and for any help you are able to provide.

Thanks,

-Liquidmettle