Trying to hide columns that have headers, but zero data in it.

[kjay05]

First off I'd like to let everyone here know that I have very limited knowledge with VBA and learning, most of which I learn from reading threads.

However, I am having problems with my last bit with just trying to hide columns after all the data has been captured from the rest of my macro. I'm trying to hide the columns that have 0 data in it and keep the ones that do open for people to see, even if there is only 1 item in it.

Here is the code that I'm using:

<code>Dim cl as Range

For Each cl In Range("A2:U2")
If cl.Value = "" Then
cl.EntireColumn.Hidden = True
Else
cl.EntireColumn.Hidden = False
End If
Next cl

End sub


When I use this code it works, however it's only reading the information from what I've stated above.

I did get help from another site with changing the "If cl.value = "" then" to
"If Application.WorksheetFunction.CountA(Columns(cl.Column).EntireColumn) = 1 Then"

Problem is it doesn't hide any of the columns. I did put it into break mode to see what was going on and for my column I that has no data in it, it would read the null but come back stating false instead of true.


Any help on this would be much appreciated.

</code>

 

[Fishboy]

First off I'd like to let everyone here know that I have very limited knowledge with VBA and learning, most of which I learn from reading threads.

However, I am having problems with my last bit with just trying to hide columns after all the data has been captured from the rest of my macro. I'm trying to hide the columns that have 0 data in it and keep the ones that do open for people to see, even if there is only 1 item in it.

Here is the code that I'm using:

<code>Dim cl as Range

For Each cl In Range("A2:U2")
If cl.Value = "" Then
cl.EntireColumn.Hidden = True
Else
cl.EntireColumn.Hidden = False
End If
Next cl

End sub


When I use this code it works, however it's only reading the information from what I've stated above.

I did get help from another site with changing the "If cl.value = "" then" to
"If Application.WorksheetFunction.CountA(Columns(cl.Column).EntireColumn) = 1 Then"

Problem is it doesn't hide any of the columns. I did put it into break mode to see what was going on and for my column I that has no data in it, it would read the null but come back stating false instead of true.


Any help on this would be much appreciated.

</code>

Hi kjay05, welcome to the boards.

One possible solution is as follows:

Sub Show_Hide_Columns()
' Defines variables
Dim Cell As Range, cRange As Range, sRange As Range, LastRow As Long


' Sets the check range as A1:U1
Set cRange = Range("A1:U1")


' For each cell in the check range
For Each Cell In cRange
    ' Defines LastRow as the last row of data for the current column
    LastRow = ActiveSheet.Cells(Rows.Count, Cell.Row).End(xlUp).Row
    ' Sets the check range as row of the current column to the last row of the current column
    Set sRange = Range(Cell.Offset(1, 0), Cells(LastRow, Cell.Column))
    ' If the number of blank cells matches the number total cells in the range then...
    If Application.CountIf(sRange, "") = sRange.Cells.Count Then
        ' Hide the column
        Cell.EntireColumn.Hidden = True
    ' Else if the blank cells does not match the total number of cells then...
    Else
        ' Show the column
        Cell.EntireColumn.Hidden = False
    End If
' Check next cell in check range
Next Cell


End Sub

 

[kjay05]

Thank you so much Fishboy! This has been frustrating me for awhile!

 

[Rick Rothstein]

Here is another macro that should also work for you...

Sub HideEmptyColumns()
  Dim Col As Range
  For Each Col In Columns("A:U")
    Col.Hidden = Application.CountA(Col) = 1
  Next
End Sub