Excel Autohide/Unhide rows based on Cell Value

Question

I have an excel form that i wan to hide rows 43 to 49 if the value in cell B22 is equal to or greater than 25000.

I have this code so far but it seems to just crash excel and i have to escape out of it.

Private Sub Worksheet_Calculate()

Rows("43:49").EntireRow.Hidden = False
Select Case Range("B22").Value
Case Is >= 25000
Rows("43:49").EntireRow.Hidden = True
Case Else
Rows("43:49").Select
Selection.EntireRow.Hidden = False

End Select
End Sub

score 1 · Accepted Answer · edited May 23 '17 at 12:14

Don't use Worksheet_Calculate. Use Worksheet_Change

See this example. Move about Worksheet_Change here

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    '~~> Change CountLarge to Count if using xl2003
    If Target.Cells.CountLarge > 1 Then Exit Sub

    Application.EnableEvents = False

    If Not Intersect(Target, Range("B22")) Is Nothing Then
        Select Case Target.Value
        Case Is >= 25000: Rows("43:49").EntireRow.Hidden = True
        Case Else: Rows("43:49").EntireRow.Hidden = False
        End Select
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Absolutely sublime :) I shall have a read of that post as well! — Matt, Nov 07 '14 at 12:56

score 0 · Answer 2 · answered Nov 07 '14 at 13:03

You could try this as well:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$22" Then
If Target.Value >= 25000 Then Rows("43:49").EntireRow.Hidden = True
If Target.Value < 25000 Then Rows("43:49").EntireRow.Hidden = False
End If
End Sub

Excel Autohide/Unhide rows based on Cell Value

2 Answers2