0

I have a folder with multiple worksheets each sheet is for a separate date. I want to hide rows based on the cell information in F5. I have managed to get this working for one sheet but not multiple.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("f5").Value = "Flat" Then
     Rows("29:62").EntireRow.Hidden = True
ElseIf Range("F5").Value <> "Flat" Then
    Rows("29:62").EntireRow.Hidden = False

End If
End Sub
cybernetic.nomad
  • 5,951
  • 15
  • 26
Gazzaw
  • 1

1 Answers1

0

Method 1:

You can use this VBA(Macro) code to hide Rows from 29 to 62 in all available Sheets in the Workbook.

Sub HideRows()

Dim sht As Worksheet
Application.ScreenUpdating = False

For Each sht In Worksheets
    beginRow = 29
    endRow = 62

    For Rowcnt = beginRow To endRow

        If Sheets("sheet1").Range("F5").Value = "Flat" Then

            sht.Rows(Rowcnt).EntireRow.Hidden = True
        Else
            sht.Rows(Rowcnt).EntireRow.Hidden = False
        End If

    Next Rowcnt
Next sht

Application.ScreenUpdating = True

End Sub

Note, instead of Rows 29:62, I've used 60:65 and Criteria is Y in A55. You may alter them.

enter image description here

enter image description here

enter image description here

How it works:

  • Copy & Paste this code as Standard Module along with Sheet1.

  • Sheet Name Sheet1, Criteria Cell reference (F5),Value Flat & Row Dimensions 29:62 are editable.

Method 2:

This Code can be used to hide Rows 29:62 in Certain Sheets only.

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.ScreenUpdating = False

    If Intersect(Target, Range("F5:F5")) Is Nothing Then Exit Sub

    If Target.Address = ("$F$5") And Target.Value = "Flat" Then
        Sheets("Sheet1").Rows("29:62").EntireRow.Hidden = True
    ElseIf Target.Address = ("$F$5") And Target.Value <> "Flat" Then
        Sheets("Sheet1").Rows("29:62").EntireRow.Hidden = False
    ElseIf Target.Address = ("$F$5") And Target.Value = "Flat" Then
        Sheets("Sheet2").Rows("29:62").EntireRow.Hidden = Ture
    ElseIf Target.Address = ("$F$5") And Target.Value <> "Flat" Then
        Sheets("Sheet2").Rows("29:62").EntireRow.Hidden = False
    End If

    Application.ScreenUpdating = True

End Sub
  • This Code may comprise Sheets of your choice to hide Rows.
Rajesh Sinha
  • 9,403