Excel Chart not Updating after Macro (Automatic is turned on)

I have a chart that does not updated after I run a macro to expand/collapse grouped rows. If I expand and collapse them manually it works fine and the chart appears fine.

Automatic calculation is turned on. The odd thing is, if I just delete a random row in the workbook, or hide/unhide random rows, the chart refreshes and the data selection series go back to what they should be instead of #VALUE!. I tried adding deleting a random row to my macro, but still nothing.

Here is what I have in my macro -

Function CountCcolor(range_data As Range, criteria As Range) As Long
Dim datax As Range
Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
If datax.Interior.ColorIndex = xcolor Then
CountCcolor = CountCcolor + 1
End If
Next datax
End Function

Sub Expand_All()
ActiveSheet.Outline.ShowLevels RowLevels:=2
Application.Calculate
End Sub

Sub collapse()
ActiveSheet.Outline.ShowLevels RowLevels:=1
Application.Calculate
End Sub


Any help appreciated. Thanks

Here's the catch with using a macro…
Running it does not normally tell Excel if any cells were changed
You have to force that

Try adding this line to the end of your macro.

Please Login or Register  to view this content.