Simplify repetitive VBA hiding row code for Excel-CodePudding

I'm very new to VBA so please bare with me.

I am looking at a way of simplifying the following code I have used to hide cells based on a certain cell value.

Here is a section of it, however it goes on right up until row 202 on one sheet (more on other sheets).

Whilst this code gives the desired result, it becomes very slow the more that is added.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("AD31").Value = "Yes" Then
        Rows("32:39").EntireRow.Hidden = False
    ElseIf Range("AD31").Value = "No" Then
        Rows("32:39").EntireRow.Hidden = True
    End If
    If Range("AD40").Value = "Yes" Then
        Rows("41:48").EntireRow.Hidden = False
    ElseIf Range("AD40").Value = "No" Then
        Rows("41:48").EntireRow.Hidden = True
    End If
    If Range("AD49").Value = "Yes" Then
        Rows("50:57").EntireRow.Hidden = False
    ElseIf Range("AD49").Value = "No" Then
        Rows("50:57").EntireRow.Hidden = True
    End If
    If Range("AD58").Value = "Yes" Then
        Rows("59:66").EntireRow.Hidden = False
    ElseIf Range("AD49").Value = "No" Then
        Rows("59:66").EntireRow.Hidden = True
    End If

...and so on

Thanks

CodePudding user response：

Perhaps this, though not sure what effect it will have on performance. I take it you've researched normal ways to speed up code.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Long

For r = 31 To 58 Step 9
    If Cells(r, "AD").Value = "Yes" Then
        Cells(r   1, 1).Resize(8).EntireRow.Hidden = True
    End If
Next r

'or possibly just
'For r = 31 To 49 Step 9
'    Cells(r   1, 1).Resize(8).EntireRow.Hidden = (Cells(r, "AD").Value = "Yes")
'Next r

End Sub

CodePudding user response：

A MS 365 alternative to @SJR 's valid answer using a Filter() formula evaluation to get the "Yes"-matching row numbers in one go.

Take care to fully qualified range references,

either by e.g. ThisWorkbook.Worksheets("Sheet1")
or a project's sheet Code(Name)

In this example I enclosed all code within a With ... - End With statement; so note that

.Cells(...) starting with a leading . refers to Sheet1,
.Evaluate starting here with a leading . marks it as a member of the Excel.Worksheet class and refers again to Sheet1. (whereas Evaluate without point prefix is a member of Excel.Application which would need another way to fully qualify the range reference)

    With Sheet1         ' project's sheet Code(Name), change to your needs
    'a) get vertical 2-dim array of found row numbers
        Dim YesRows As Variant
        YesRows = .Evaluate("=Filter(Row(AD:AD),AD:AD=""Yes"")")

        'b) hide following rows
        Dim i As Long
        For i = 1 To UBound(YesRows)
            .Cells(YesRows(i, 1)   1, 1).Resize(8).EntireRow.Hidden = True
        Next
    End With