VBA Show only rows with FALSE value

[radsok8199]

Dear VBA Masters

I want below code to check range and If any row contains value FALSE then EntireRow.Hidden = False , else I want rest of rows to be hidden.
Seems simple but it does not work the way I want. Below code shows only 2 rows from below example. Why ?

Sub ShowFalseOnly()

Dim Cell As Range

    For Each Cell In ActiveSheet.Range("A1:F14")
        If IsError(Cell.Value) Then
        Cell.EntireRow.Hidden = True
    ElseIf Cell.Value = True Then
        Cell.EntireRow.Hidden = True
    ElseIf Cell.Value = False Then
       Cell.EntireRow.Hidden = False
    End If
    Next Cell
End Sub

 

[JamesCanale]

It's going through every cell in your range, so really the only thing that matters is what the last column of data says.

What do you want to happen? A false in any column and the row is not hidden?

If so, you'll have to go through the data set row by row, not just cell by cell.

 

[Joe4]

I would recommend looping through the rows, not the individual cells, and use the COUNTIF function to count the number of FALSE entries in that row.
If it is 0, then hide the row. Otherwise do not.

 

[Marc L]

Hi,​

​

could be directly achieved without looping just with a couple of codelines with an advanced filter and a criteria formula like any Excel beginner operating manually …​

 

[radsok8199]

I would recommend looping through the rows, not the individual cells, and use the COUNTIF function to count the number of FALSE entries in that row.
If it is 0, then hide the row. Otherwise do not.

Great idea Thanks for the tip.
I wrote code but something goes wrong. Any idea what i am doing wrong here ?

Sub ShowFalseOnly()

    Dim WS   As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim r As Long
    Dim c As Long
    Dim result As Long
    Dim rng As Range
    
    
    Set WS = ActiveWorkbook.Sheets("Sheet1")
        lastRow = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row
            lastCol = WS.Cells(1, Columns.Count).End(xlToRight).Column
                Set rng = WS.Range(WS.Cells(2, 1), WS.Cells(lastRow, lastCol))
                   
        For c = 1 To lastCol
             For r = 2 To lastRow
             
            result = Application.CountIf(WS.Range(WS.Cells(r, c)), "FALSE")
              
    If result = 0 Then
    rng.EntireRow.Hidden = False
    ElseIf result > 0 Then
    rng.EntireRow.Hidden = True
    End If
    
    Next c
        Next r
End Sub

 

[Marc L]

​

WIth Excel basics at beginner kid level so without any loop, just a formula & an advanced filter needing a bunch of two VBA codelines only …​

 

[Joe4]

OK, you have a lot of extra stuff in there you do not need. Since we are using COUNTIF to count the total number of FALSE entries in the whole row, we do NOT need to loop through the columns, just the rows. Also, since it looks like your TRUE/FALSE values are boolean values and not text values, you do NOT want quotes around the word FALSE (only use quotes for text values). And when finding the last column, since we are starting in the last column, we want "xlToLeft", not "xlToRight".

Here is a cleaned-up version of the code. Instead of using all your "WS" worksheet references, I simply selected the sheet first, so all references are to that sheet inherently.

Sub ShowFalseOnly()

    Dim lastRow As Long
    Dim lastCol As Long
    Dim r As Long
    Dim rng As Range
   
    Application.ScreenUpdating = False
   
    Sheets("Sheet1").Activate
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
                  
    For r = 2 To lastRow
        Set rng = Range(Cells(r, 1), Cells(r, lastCol))
        If Application.CountIf(rng, False) > 0 Then
            rng.EntireRow.Hidden = False
        Else
            rng.EntireRow.Hidden = True
        End If
    Next r
       
    Application.ScreenUpdating = True
   
End Sub

As has been mentioned, there are other ways of doing it too.

 

[radsok8199]

OK, you have a lot of extra stuff in there you do not need. Since we are using COUNTIF to count the total number of FALSE entries in the whole row, we do NOT need to loop through the columns, just the rows. Also, since it looks like your TRUE/FALSE values are boolean values and not text values, you do NOT want quotes around the word FALSE (only use quotes for text values). And when finding the last column, since we are starting in the last column, we want "xlToLeft", not "xlToRight".

Here is a cleaned-up version of the code. Instead of using all your "WS" worksheet references, I simply selected the sheet first, so all references are to that sheet inherently.

Sub ShowFalseOnly()

    Dim lastRow As Long
    Dim lastCol As Long
    Dim r As Long
    Dim rng As Range
  
    Application.ScreenUpdating = False
  
    Sheets("Sheet1").Activate
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
                 
    For r = 2 To lastRow
        Set rng = Range(Cells(r, 1), Cells(r, lastCol))
        If Application.CountIf(rng, False) > 0 Then
            rng.EntireRow.Hidden = False
        Else
            rng.EntireRow.Hidden = True
        End If
    Next r
      
    Application.ScreenUpdating = True
  
End Sub

As has been mentioned, there are other ways of doing it too.

Thank You. Works great. I see clearly now why my thinking was wrong.

 

[Joe4]

Thank You. Works great. I see clearly now why my thinking was wrong.

You are weclome.
Glad I was able to help!