In the comments of Choice of Category Axis Order a reader asked how to use only part of a range as the source data for a chart. Her data and chart initially looked like this:
The line “Total Other Items” is a sum of the items below it in the list, and the chart does not show this subtotal. The series formula looks like:
Notice both the X and Y arguments each consist of two ranges, separated by commas, and surrounded by parentheses. The reader inserted some items, both above and below the subtotal line:
The chart does not show the updated data. The series formula looks like:
The reader wanted some way to show all of the individual items, and not the subtotal, dynamically or programmatically, to avoid the tedium of updating the chart manually whenever the range was changed. I suggested two ranges, one without the subtotal for charting, and one with the subtotal for the boss. My suggestion was rejected.
I dusted off an old VBA routine which I had originally written to keep zero and blank values out of a chart, and modified it to keep the subtotal line out. The routine redefined some names in the worksheet, and the names were used in the chart. The thing to do is run the routine once, then adjust the chart series formula to this:
Now whenever thee VBA code is run, it redefines myARange and myBRange. Since the chart series is defined by these, running the code indirectly changes the chart as well. Here is a chart of the table above, updated with the changed names:
Assuming the data is in columns A and B, with headers in row 1 and actual data starting in row 2, and nothing clutters rows A and B beneath the data. This is the macro which, when run whenever the table is changed, will update the chart.
Sub FilterChartSourceDataRange() Dim myCell As Range Dim myARange As Range, myBRange As Range Dim nRow As Long, iRow As Long Dim sAvoid As String sAvoid = "Total Other Items" ' Find last row nRow = ActiveSheet.Cells(65536, "A").End(xlUp).Row For iRow = 2 To nRow Set myCell = ActiveSheet.Cells(iRow, "A") If myCell.Value <> sAvoid And Not IsEmpty(myCell) Then ' cell is okay, so add to charting ranges If myARange Is Nothing Then Set myARange = myCell Set myBRange = myCell.Offset(0, 1) Else Set myARange = Union(myARange, myCell) Set myBRange = Union(myBRange, myCell.Offset(0, 1)) End If End If Next ' redefine ranges in worksheet ActiveWorkbook.Names.Add Name:="myARange", _ RefersTo:="='" & ActiveSheet.Name & "'!" & myARange.Address ActiveWorkbook.Names.Add Name:="myBRange", _ RefersTo:="='" & ActiveSheet.Name & "'!" & myBRange.Address ' clean up Set myCell = Nothing Set myARange = Nothing Set myBRange = Nothing End Sub