CODEWITHSUNDEEP
excelvba
Baeblay'd
Baeblay'd

Reputation: 3

Hide rows based on False value

I am using Excel and VB to make a worksheet that hides rows of information based on a checkbox. What I have already done is set up a column "U" so that it displays whether or not the box is checked (true/false). I need to create a macro so that if the box is false (unchecked), it will be hidden. Here is what I have so far:

Sub HideCells()

   If Range("U2").Value = "False" Then
       Rows("2:2").EntireRow.Hidden = True
   ElseIf Range("U2").Value = "True" Then
       Rows("2:2").EntireRow.Hidden = False
   End If

End Sub

Now this works for a single row, but how can I extend the range from just U2 to U2:U300?

Thanks.

Upvotes: 0

Views: 1404

Answers (2)

JohnyL
JohnyL

Reputation: 7142

You can reduce Emily's answer down to one line. Don't forget to switch off screen updating:

Sub HideCells()
    Dim r&
    Application.ScreenUpdating = False
    For r = 2 To 300
        Rows(i).EntireRow.Hidden = Not CBool(Cells(r, "U"))
    Next
    Application.ScreenUpdating = True
End Sub

But the fastest way would be to use AutoFilter. If you're interested, I can update answer to include this code.

UPDATE - FASTER WAY

As I stated above, there's much faster way to achieve hiding rows by using Autofilter:

Sub HideCellsFast()
    
    Dim rngTable As Range    '//Range with header
    Dim rngData As Range     '//rngTable's body
    Dim rngFiltered As Range '//Filtered rows
    
    '// Set original range including header
    Set rngTable = Range("U1:U10")
    '// Get range without header
    With rngTable
        Set rngData = .Offset(1).Resize(.Rows.Count - 1)
    End With
    
    '// Filter rows
    rngTable.AutoFilter Field:=1, Criteria1:=1
    
    '// Use error hanlder since there can be no filtered rows
    On Error Resume Next
    '// Try to get filtered rows
    Set rngFiltered = rngData.SpecialCells(xlCellTypeVisible)
    '// Check if we have filtered rows
    If Err = 0 Then
        '// If we're here, there are filtered rows.
        '// First, remove filter.
        rngTable.AutoFilter Field:=1
        '// Then hide the rows we have filtered
        rngFiltered.EntireRow.Hidden = True
    Else
        '// Do something if we have no filtered rows
    End If
    On Error GoTo 0
    
    '// Other code...

End Sub

Upvotes: 1

Emily Alden
Emily Alden

Reputation: 570

To make a basic loop from the second row to row 300 we define a new variable I which will hold the current value/row. Then we replace our row reference with the value i and apply a loop around it. 

Sub HideCells()
 Dim i As Long
For i = 2 to 300
   If Range("U" & i).Value = "False" Then
       Rows(i).EntireRow.Hidden = True
   ElseIf Range("U" & i).Value = "True" Then
       Rows(i).EntireRow.Hidden = False
   End If
 Next i

End Sub

Upvotes: 0

Related Questions