Hide columns that have whole rows TRUE

[Dani_LobP]

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:

    .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!

 

[offthelip]

try this:

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

 

[Dani_LobP]

try this:

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

If Not (inarr(i, col)) Then

 

[offthelip]

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:

If Not (inarr(i,col)="TRUE") then

 

[Dani_LobP]

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:

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.

 

[offthelip]

if there only 3 possibliities as you specified you can change the test to:

if (inarr(i,col)="False") then

 

[Dani_LobP]

if there only 3 possibliities as you specified you can change the test to:

if (inarr(i,col)="False") then

This made it work as expected!

great, thanks mate!!