Hidden Object Range Failure in Worksheet Calculate

[ravi1129]

I have a formula in Sheet2 named "Parameters" in cell D69.
This formula is pulling info from sheet 3 & sheet 4.

My excel keeps crashing with a Run-time error: Method 'Hidden' of object 'Range' failed.
I don't see how it could be in a loop?



In my sheet 2 - here is my vba code:

Option Explicit
Public OldValuetoTrackChange1 As Variant


Private Sub Worksheet_Calculate()
Dim CellNAErrorPresent As Boolean
CellNAErrorPresent = IsError(Range("D69").Value)
' If there is a #N/A error then don't execute below and set this temp variable to 0
If CellNAErrorPresent = False Then
If Range("D69").Value <> OldValuetoTrackChange1 Then
OldValuetoTrackChange1 = Range("D69").Value
End If
Else
OldValuetoTrackChange1 = 0
End If

' if my cell D69 is = "None" and not #N/A from the formula then hide the next two rows
If OldValuetoTrackChange1 = "None" Then
Rows("70:71").EntireRow.Hidden = True
Else
Rows("70:71").EntireRow.Hidden = False
End If
End Sub


In my ThisWorkbook - here is my code:

Private Sub Workbook_Open()
OldValuetoTrackChange1 = Sheets("Parameters").Range("D69").Value
End Sub

 

[ravi1129]

The Rows (70&71) I am trying to Hide have formulas in them that are pulling info from sheet 3 & 4 as well. Is that causing the issue? How do I avoid crashing in this scenario?

 

[ravi1129]

Figured it out:

Needed to add
Application.EnableEvents = True


Application.EnableEvents = True

Application.EnableEvents = False</pre></pre>

 

[Dryver14]

have you tried qualifying the rows activesheet.rows or sheet name.rows

 

[ravi1129]

Needed to add
Application.EnableEvents = True at the beginning after setting variables
and
Application.EnableEvents = False at the end before End Sub

 

[Dryver14]

OK glad you are sorted