Hide/Unhide rows based on multiple cell values

Question

I need an assistance for my issue. How to include two different criterias into this code to make my rows hidden/unhiden, if I one criteria is TRUE/FALSE and another is Drop-down with some values? For the first criteria, with True/False, if the True is selected then hide all False rows, and for another criteria where I have drop-down, the only thing I wanted to have is when I choose 0 as a value to unhide all rows, to bring back my original state of data.

p.s True/False rows is a "helper column" where I am actually filter another values (from drop down). If I choose TRUE then I am having all needed values and if it is FALSE then I am excluding them from the list.

Sub Hide_Unhide_Rows()

  If Range("B3").Value = "Passed" Then
    Rows("5:10").EntireRow.Hidden = True
  ElseIf Range("B3").Value = "Failed" Then
    Rows("5:10").EntireRow.Hidden = False
  End If

End Sub

Your post has no question, you need to ask one (see How to Ask). Also you should explain what you tried and what the issue is that stops you from writing that code. A list of requirements is not actually a question. — Pᴇʜ
Either use filters (because that is what you are actually trying to do) or you somehow need to loop through your rows to check which are false and hide each one of them. But you need to come up with some attempt or you need to explain why you could not accomplish it and where exactly you got stuck. • Otherwise this is a "here is my code please fix it for me" question. — Pᴇʜ

VBasic2008 VBasic2008 · Accepted Answer · 2021-03-29T07:56:56

Toggle Hide/Unhide

Adjust the Range("B3") and the constants in the second procedure.

Sheet Module e.g. Sheet1

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B3"), Target) Is Nothing Then
        toggleHide Range("B3")
    End If
End Sub

Standard Module e.g. Module1

Option Explicit

Sub toggleHide(ByVal CellRange As Range)
    
    Const RowsAddress As String = "5:10"
    Const cCol As String = "G"
    Const Crit As Boolean = False
    
    With CellRange.Worksheet
        If .AutoFilterMode Then .AutoFilterMode = False
        Dim rg As Range: Set rg = .Rows(RowsAddress)
        
        Select Case CellRange.Value
        Case "Hide"
            Dim drg As Range
            Set drg = rg.Columns(cCol).Offset(-1).Resize(rg.Rows.Count + 1)
            drg.AutoFilter 1, Crit
            If WorksheetFunction.Subtotal(103, drg) > 1 Then
                Dim frg As Range: Set frg = rg.SpecialCells(xlCellTypeVisible)
                .AutoFilterMode = False
                frg.EntireRow.Hidden = True
            End If
        Case "Show"
            rg.Hidden = False
        'Case Else
        End Select
    
    End With

End Sub

Hide/Unhide rows based on multiple cell values

1 Answers

Toggle Hide/Unhide