Reputation: 1703
I have some data that I'm putting into a chart and formatting. There are some business rules where some of the data is "Protected" like in the example. The issue when graphing with the word "Protected" (or any other word) excel is graphs that point at the bottom of the X-Axis. I'd like the graph to look like the 2nd chart, but I'd like to avoid graphing off a hidden column because I think it would confuse my power users.
Any thoughts or insights are appreciated!
Upvotes: 13
Views: 1270
Reputation: 406
I'm kind of gutted that I couldn't figure the majority of this out, it seems like such a simple premise. I've made somewhat of a workaround but it could definitely do with some refinement;
I'm using a VBA function called Eval:
Public Function Eval(varRange As Range)
Dim varArray() As Variant
varArray = varRange
Dim R As Long
Dim C As Long
For R = 1 To UBound(varArray, 1)
For C = 1 To UBound(varArray, 2)
If varArray(R, C) = "Protected" Then
varArray(R, C) = CVErr(xlErrNA)
End If
Next C
Next R
Eval = varArray
End Function
And a defined range called 'Chart' which has the following formula
=EVAL(INDIRECT("Sheet1!$B$2:$B$"&1+COUNTA(Sheet1!$B$2:$B$31)))
This makes the range dynamic, but you could just use =EVAL(B2:B6)
if need be.
On your chart, edit the series values range to Sheet1.xlsm!Chart
(or whatever your sheet is called), and you get the data range with any "Protected" values converted to blanks. The only thing is, the chart will plot the line between the two points regardless, I cannot make the chart plot the gap. Maybe someone with better google-fu than myself could come up with a solution?
Upvotes: 0
Reputation: 2119
When manually building the chart:
Here are my results ...
A small bit of VBA that will generate the chart ...
Sub MakeChart()
Dim cell As Range, mySerRng As Range, mySrcRng As Range
Dim mySht As Worksheet, myChrt As Chart
Dim lastRow As Long
Set mySht = Worksheets("Sheet1")
lastRow = mySht.Range("A" & mySht.Rows.Count).End(xlUp).Row
Set mySerRng = mySht.Range(mySht.Cells(1, 2), mySht.Cells(lastRow, 2))
Set mySrcRng = mySht.Range(mySht.Cells(1, 1), mySht.Cells(lastRow, 2))
Set myChrt = mySht.Shapes.AddChart2(-1, xlLine, mySht.Range("C1").Left, mySht.Range("C1").Top).Chart
With myChrt
.SeriesCollection.Add Source:=mySrcRng, RowCol:=xlColumns, serieslabels:=True, categorylabels:=True, Replace:=True
For Each cell In mySerRng
If cell.Value = "Protected" Then
.SeriesCollection(1).Points(cell.Row - 1).Format.Line.Visible = False
.SeriesCollection(1).Points(cell.Row).Format.Line.Visible = False
End If
Next cell
End With
End Sub
Alternate approach
Build a scatter chart with multiple series, separated by the "offending" rows, and formatted so they appear to be one series ...
This has disadvantages:
Upvotes: 6
Reputation: 455
While I agree with @OldUgly's answer, you could also just add another column next to the data, if you think it would be understandable.
=IF(ISNUMBER($B2),$B2,"")
That should do the trick.
Upvotes: 0