Hide rows based on cell value on another sheet (including loop)

Question

I'm trying to hide/unhide rows in "Output" sheet conditional on cell values in "Input" sheet. That in itself isn't the most difficult part, but I'm having troubles with the loop as I need to hide 300 rows based on 300 cell values individually.

So for example, I would like to hide row 12 in "Output" sheet if cell B6 in "Input" sheet contains a "No". Then I would like to hide row 13 in "Output" sheet if cell B7 in "Input" sheet contains a "No"... all the way until row 306 and B300.

I've written the following code so far but it doesn't seem to be working. Any help is very much appreciated!

Sub HideRow()

Dim i As Integer, j As Integer

For i = Sheets("Input").Range("B6") To Sheets("Input").Range("B300")
    For j = Sheets("Output").Rows("12") To Sheets("Output").Rows("306")

        If Sheets("Input").Range("i").Value = "No" Then
            Sheets("Output").Rows("j").EntireRow.Hidden = True
        Else
            Sheets("Output").Rows("j").EntireRow.Hidden = False

        Next j

    Next i

End Sub

For i = Sheets("Input").Range("B6") To Sheets("Input").Range("B300") should be replaced with For i = 6 to 300. The same for the next iteration. Then, your code will hide all "Output" sheet rows if the condition is true... Can you better explain in which conditions the rows in "Output" sheet must be hidden? Range i das not mean anything for VBA. Please explain (in words) what you want to accomplish. Do you need to hide the same row of the other sheet, if the condition in the first one is true? — FaneDuru
Thanks for the quick replies! If cell B6 in "Input" sheet equals "No", then row 12 in "Output" sheet has to be hidden. Same goes for B7 in "Input" if it equals "No" then row 13 in "Output should be hidden. Repeat all the way up to B300 in "Input" and row 306 in "Output". Hope that clarifies a bit! — Nicko

Tim Williams Tim Williams · Accepted Answer · 2020-06-16T19:03:04

You need to specify a column where the "No" value might be found.

For example:

Sub HideRows()
    Dim i As Long
    For i = 6 To 300
        Sheets("Output").Rows(i + 6).EntireRow.Hidden = _
               (Sheets("Input").Cells(i, "A").Value = "No") 'for example using ColA
    Next i
End Sub

Hide rows based on cell value on another sheet (including loop)

1 Answers