Private Sub Worksheet_Calculate()

[nm079]

Hi,

I am currently struggling with a Private Sub Worksheet_Calculate() macro.

I have 3 areas of my worksheet that I wish to hide / unhide under different conditions.

Unfortunately I am struggling to get 3 'IF' statements into the one sub.

Here's what I have:

Private Sub Worksheet_Calculate()

If Range("B70").Value = "No" Then

Range("71:71", "73:73").EntireRow.Hidden = True

Else

Range("71:71", "73:73").EntireRow.Hidden = False

End If

If Range("F94").Value = "No" Then

Range("86:86", "92:92").EntireRow.Hidden = True

Else

Range("86:86", "92:92").EntireRow.Hidden = False

End If

If Range("E94").Value = "No" Then


Range("68:68", "75:75").EntireRow.Hidden = True
Range("84:84", "86:86").EntireRow.Hidden = True

Else

Range("68:68", "75:75").EntireRow.Hidden = False
Range("84:84", "86:86").EntireRow.Hidden = False

End If

End Sub

I have also tried nesting the IF commands - but unfortunately only one of the conditions seem to work.

If anybody could shed some light on this I'd be really grateful as I've spent the past 2 days banging my head against a brick wall!

 

[Joe4]

Welcome to the Board!

How is it not working?
What is (or isn't happening)?
What is happening that is supposed to trigger this macro?

Note that if you do NOT want to hide rows 72 along with 71 and 73, this will not work properly:

Range("71:71", "73:73").EntireRow.Hidden = True

It should look like this:

Range("71:71, 73:73").EntireRow.Hidden = True

 

[nm079]

Thanks for your reply and welcome!

Two of the IF statements are working, however the middle IF doesn't seem to do anything:

If Range("F94").Value = "No" Then

Range("86:86", "92:92").EntireRow.Hidden = True

Else

Range("86:86", "92:92").EntireRow.Hidden = False

End If

The hide function is correct - I wish to hide a block of rows rather than just two.

For the record - I am using the calculate function as the target fields are set by a formula.

 

[nm079]

Sorry - ignore that last reply.

It's the first IF statement that doesn't work.

When B70 is set to 'No' it should hide rows 71-73.

It does nothing.

The other IF statements hide their rows correctly based on the values in row 94.


Sorry for the confusion!

 

[nm079]

Solved.

 

[Joe4]

Glad you got it figured out.

Note if you are looking to hide a block of rows, you can rewrite this:

Range("71:71", "73:73").EntireRow.Hidden = True

to this:

Rows("71:73").EntireRow.Hidden = True

Its a little shorter, and also a little clearer of what is happening.