Hidden Object Range Failure in Worksheet Calculate

ravi1129

New Member
Joined
Mar 2, 2018
Messages
4
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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?
 
Upvote 0
Figured it out:

Needed to add
Application.EnableEvents = True


Application.EnableEvents = True

Application.EnableEvents = False</pre></pre>
 
Upvote 0
Needed to add
Application.EnableEvents = True at the beginning after setting variables
and
Application.EnableEvents = False at the end before End Sub
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,194
Members
453,021
Latest member
pingpong7117

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top