Thread: Chart integrity degrades when hiding

I have a macro that is hiding rows. The charts and the information that is on them is included in these rows. These are XY scatter charts. I have changed the properties of the chart to "don't move or size with cells." However, it still isn't completely working.

If a group of cells is hidden by the macro and then I make a new worksheet by right clicking the worksheet tab and selecting "move or copy," and then the macro unhides the same group of cells, the equasion for trendline and R-Squared value dissapears. Instead, there are "x value" data labels on each point of the line.

After some time and repetition of this, I even had on chart take on the properties (including source information) of another chart in the same worksheet.

Can this problem be fixed?

Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("B20,J20")) Is Nothing Then Exit Sub
Select Case Target.Address(0, 0)
    Case "B20"
        Select Case Target.Value
        Case 0.98, 1.4
            Rows("156:186").EntireRow.Hidden = False
            Rows("187:217").EntireRow.Hidden = True
            ActiveSheet.PageSetup.PrintArea = "$A$1:I$186"
        Case 0.76
            Rows("187:217").EntireRow.Hidden = False
            ActiveSheet.PageSetup.PrintArea = "$A$1:$I$155, $A$187:$I$217"
            Rows("156:186").EntireRow.Hidden = True
        Case Else
            Rows("156:217").EntireRow.Hidden = True
            ActiveSheet.PageSetup.PrintArea = "$A$1:$I$155"
        End Select
    Case "J20"
        Select Case Target.Value
        Case 1
            Rows("1:186").EntireRow.Hidden = False
            Rows("187:217").EntireRow.Hidden = True
            ActiveSheet.PageSetup.PrintArea = "$A$1:I$186"
        Case 2
            Rows("94:124").EntireRow.Hidden = True
            Rows("156:217").EntireRow.Hidden = True
            ActiveSheet.PageSetup.PrintArea = "$A$1:$I$155"
        Case Else
            Rows("1:155").EntireRow.Hidden = False
            Rows("156:217").EntireRow.Hidden = True
            ActiveSheet.PageSetup.PrintArea = "$A$1:$I$155"
        End Select
End Select
End Sub

I thought that this might be complex enough that I should include a file for the example. So here is a simplified Excel file with the charts and macro. Go ahead and take a look.

bump, no response.

The trend line label does appear to vanish when copying to another sheet when the original is not displayed.

I guess the fix is to unhide the cells prior to copying sheet and then hide on new sheet.

Yea, I can unhide and then rehide, but I was hoping there might be a more automated way of fixing the problem.