Hide columns that have whole rows TRUE

Dani_LobP

New Member
Joined
Aug 16, 2019
Messages
45
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!
 

Some videos you may like

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
Joined
Dec 23, 2017
Messages
1,071
Office Version
2010
Platform
Windows
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

New Member
Joined
Aug 16, 2019
Messages
45
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
Joined
Dec 23, 2017
Messages
1,071
Office Version
2010
Platform
Windows
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

New Member
Joined
Aug 16, 2019
Messages
45
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
Joined
Dec 23, 2017
Messages
1,071
Office Version
2010
Platform
Windows
if there only 3 possibliities as you specified you can change the test to:
Code:
if (inarr(i,col)="False") then
 

Forum statistics

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

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top