Hide columns that have whole rows TRUE

Dani_LobP

Board Regular
Joined
Aug 16, 2019
Messages
126
Office Version
  1. 365
Platform
  1. Windows
Hello all,

I have some table build up from different formulas and so on.

Final result is some huge sheet filled with columns and lots of rows showing just True or False.

I'd like to know how i could hide all columns that are all their values True, so i can just end up with just the False items.

For the rows is easy because i just applied a filter. But can't seem to find a way to hide the column.

Was thinking about maybe making a sum for the whole column, and if its = 0, then hide column.

Something like this, but obviously giving error in the countif line:

Code:
    .Range("B2").Formula = "=IFERROR(INDEX('Sheet1'!AB:AB,$A2)='Sheet2'!Q2,""NF"")"
    .Range("B2").AutoFill Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Range("B2").Select
    .Range(Selection, Selection.End(xlDown)).Select
    .Selection.Offset(1, 0).Formula = "=COUNTIF(B2:B & LastRow1,FALSE)"   <- LastRow1 been declared earlier
    If Range("B2").End(xlDown) = 0 Then
            Range("B2").EntireColumn.Hidden = True
    End If

Columns i'd like to apply this, go from B to V, so eventually many of those will end up hidden.

thanks in advance!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
try this:
Code:
inarr = Range(Cells(1, 1), Cells(lastrow1, 22))
For col = 2 To 22
    alltrue = True
    For i = 1 To lastrow1
     If Not (inarr(i, col)) Then
      alltrue = False
      Exit For
     End If
    Next i
    If alltrue Then
     Range(Cells(1, col), Cells(1, col)).EntireColumn.Hidden = True
    End If
 Next col
 
Last edited:
Upvote 0
try this:
Code:
inarr = Range(Cells(1, 1), Cells(lastrow1, 22))
For col = 2 To 22
    alltrue = True
    For i = 1 To lastrow1
     If Not (inarr(i, col)) Then
      alltrue = False
      Exit For
     End If
    Next i
    If alltrue Then
     Range(Cells(1, col), Cells(1, col)).EntireColumn.Hidden = True
    End If
 Next col

Thanks for the help!

I tried this, but keep getting error 13 mysmatch in this line

Code:
If Not (inarr(i, col)) Then
 
Upvote 0
that is because Inarr(i,col) was assumed to be a boolean, it would appear that sometimes there is something in the cell that is not TRUE or FALSE, so I suggest trying changing it to:
Code:
If Not (inarr(i,col)="TRUE") then
 
Upvote 0
that is because Inarr(i,col) was assumed to be a boolean, it would appear that sometimes there is something in the cell that is not TRUE or FALSE, so I suggest trying changing it to:
Code:
If Not (inarr(i,col)="TRUE") then

yeah, there is TRUE, FALSE or NF when there is not found item of those im comparing.

So i guess the condition needs to be IF all column is TRUE and/or NF then hide the column.

Right now since there are NF in some rows, it won't apply the hide.
 
Upvote 0
if there only 3 possibliities as you specified you can change the test to:
Code:
if (inarr(i,col)="False") then
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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
Back
Top