If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|