VBA Show only rows with FALSE value

radsok8199

New Member
Joined
Dec 4, 2020
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
  3. Mobile
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 ?

1.PNG


2.PNG


VBA Code:
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
 
Last edited:

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

JamesCanale

Board Regular
Joined
Jan 13, 2021
Messages
159
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,949
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 5, 2021
Messages
508
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Dec 4, 2020
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
  3. Mobile

ADVERTISEMENT

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 ?

VBA Code:
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

Well-known Member
Joined
Apr 5, 2021
Messages
508
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,949
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
VBA Code:
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.
 
Solution

radsok8199

New Member
Joined
Dec 4, 2020
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
  3. Mobile
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.
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top