VBA Code for hiding rows based on a result of formula

[kopower]

I am new to VBA and am trying to hide rows automatically based upon a result of a formula.

cell is AB11 and if the result is false then hide rows 36:40
if AB11 result is true unhide rows 36:40

I have tried this:

Private Sub Worksheet_Calculate()
Dim MyRange As Range
Set MyRange = Range("AB11")
If MyRange.Value = "FALSE" Then
Rows("36:42").EntireRow.Hidden = True
Else
Rows("36:42").EntireRow.Hidden = False
End If
End Sub

But it does not work, hoping someone can help me.

[mancubus]

welcome to the forum.

try removing quotation marks before and after FALSE.

and use code tags when posting code. # button will do it for you.

like:


Private Sub Worksheet_Calculate()
If Range("AB11").Value = False Then
Rows("36:42").EntireRow.Hidden = True
Else
Rows("36:42").EntireRow.Hidden = False
End If
End Sub

[SamT]

Rows("36:42").EntireRow.Hidden = TrueYou don't need the EntireRow Property. Rows(n) is the entire row(s).



Rows("36:42").Hidden = True

[kopower]

I tried removing the quotation marks and it does not work. I also tried removing EntireRow Property.Rows(n) also did not work.

[kopower]

I just tried it an again and the strange thing is it works on Excel for Mac but it does not work on 2010 Excel for Windows. What am I doing wrong, it just doesn't make sense.

[kopower]

this is the Error Message: "Run-time error "1004" Unable to set the Hidden property of the Range class" and it is yellow highlight around Rows("34:46").Hidden = True

[SamT]

removing EntireRow Property.Rows(n)
Not sure what that refers to.

I'm using Excel 2003 on Windows. Things may be different.

I'm always learning new things about the newest Excel. I would upgrade, but this is only a hobby now.

[Aussiebear]

What about this then


Private Sub Worksheet_Calculate()
If Range("AB11").Value = False Then
Rows("36:42").Visible = True
Else
Rows("36:42").Visible = False
End If
End Sub

[mancubus]

is Calculation Options set to Automatic?

attached is a working example. i just used B11 instead od AB11.

[mancubus]

@ SamT,

i personally use Rows object to refer to a row or rows.
but if i copy an existing code, i sometimes modify it and sometimes not.


@ Aussiebear,
in this case, Rows("1") and Rows("1").EntireRow do the same.

Sub test_EntireRow()
MsgBox _
Rows("1").Address & vbLf & _
Rows("1").EntireRow.Address & vbLf & _
Range("1:1").Address & vbLf & _
Range("1:1").EntireRow.Address
End Sub