# Hide columns that have whole rows TRUE

#### Dani_LobP

##### New Member
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.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### offthelip

##### Well-known Member
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:
• Dani_LobP

#### Dani_LobP

##### New Member
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``

#### offthelip

##### Well-known Member
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``

• Dani_LobP

#### Dani_LobP

##### New Member
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.

#### offthelip

##### Well-known Member
if there only 3 possibliities as you specified you can change the test to:
Code:
``if (inarr(i,col)="False") then``

• Dani_LobP

#### Dani_LobP

##### New Member
if there only 3 possibliities as you specified you can change the test to:
Code:
``if (inarr(i,col)="False") then``
This made it work as expected!

great, thanks mate!!

1,089,389
Messages
5,407,964
Members
403,174
Latest member
xdresch