CODEWITHSUNDEEP
excelvba
Nicko
Nicko

Reputation: 1

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

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

Upvotes: 0

Views: 1408

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

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

Upvotes: 1

Related Questions