Line Equation and R squared disappear
 

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 equation 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 (an automated fix that is)?

The macro code:

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.9, 0.94, 0.76, 0.75, 0.78
Rows("32:93").EntireRow.Hidden = False
Rows("94:124").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:I$93"
Case 0.84
Rows("32:124").EntireRow.Hidden = False
Rows("63:124").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$62"
Case Else
Rows("32:124").EntireRow.Hidden = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$124"
End Select
Case "J20"
Select Case Target.Value
Case 2
Rows("32:124").EntireRow.Hidden = False
Rows("63:124").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:I$62"
Case 3
Rows("63:124").EntireRow.Hidden = False
Rows("94:124").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:I$93"
Case 1
Rows("32:124").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:I$31"
Case Else
Rows("32:124").EntireRow.Hidden = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$124"
End Select
End Select
End Sub


Excel file with the macro can be found here (Login is required to access.):

http://www.excelforum.com/attachment...g-minerals.xls