Skip data in excel bar chart

Question

I have a big series of data in excel and I want to plot it using a bar chart. The trick is that I need to skip the error cells, not as 0 or gaps, but just not adding the series:

Column A   Column B
   A          10
   B          15
  na()        na()
   D          6

...and I want my plot to have A, B and D (WITHOUT THE C) on the y axis and values in the x axis.

This is just a simplification of course...the data set is much bigger, that is why I cannot hide rows or stuff like that

Thanks!

Ralph Ralph · Accepted Answer · 2016-06-02T12:07:57

To hide a series just hide the underlying row / column. So, in your case you can simply hide the column C:

ThisWorkbook.Worksheets(1).Columns("C").EntireColumn.Hidden = True

If you have a range for your series then you can scan it for errors and hide the ones that contain errors with the following sub:

Sub tmpTest()

Dim cell As Range

For Each cell In ThisWorkbook.Worksheets(1).Range("A1:Z100")
    If VarType(cell.Value) = vbError Then ThisWorkbook.Worksheets(1).Columns(cell.Column).EntireColumn.Hidden = True
Next cell

End Sub

Skip data in excel bar chart

1 Answers