Maybe this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address(True, True) = "$A$2" Then
        Select Case Target.Value
            Case "Yes"
                Call HideRows
                
            Case "No"
                Call UnHideRows
        End Select
    End If
End Sub

Sub HideRows()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    BeginRow = 4
    EndRow = Range("A" & Rows.Count).End(xlUp).Row
    ChkCol = 9

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = "" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Sub UnHideRows()
Cells.EntireRow.Hidden = False
'    BeginRow = 1
'    EndRow = 2953
'    ChkCol = 9
'
'    For RowCnt = BeginRow To EndRow
'        If Cells(RowCnt, ChkCol).Value = "" Then
'            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
'        End If
'    Next RowCnt
End Sub

Note there are a few changes to both subroutines.


Regards, TMS