VBA for if cell in range is blank, then hide the colulmn

SevenOut

New Member
Joined
Jan 7, 2020
Messages
32
Office Version
2013
Platform
Windows
I have a workbook with tabs for each month. The range is the same for each monthly worksheet:
B2:AF2 - These are the days of month, formula populates each cell in range until the end of month. Feb would have a couple blanks for example as the range extends for 31 possible days.
If a cell in range B2:AF2 is blank, I would like to hide that column(s) automatically.
The range of dates is auto populated based on the year so I would not need to toggle a button to show/hide.
There are other worksheets in the workbook so globally would not work, just the monthly worksheets:
January
February
March
and so on...
Given the above for February, AE and AF should be auto hidden (2/30 and 2/31 do not exist, therefor blank).
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
4,790
Office Version
2013
Platform
Windows
@SevenOut
Assuming there are no blanks other than at the end of months with less than 31 days and, that all columns are visible at the time of running then try...

VBA Code:
Sub HideBlanks()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets

    Select Case LCase(Trim(ws.Name))
        Case "february", "april", june, "september", "november"
            For c = 30 To 32
            If Len(ws.Cells(2, c).Value) = 0 Then ws.Cells(2, c).EntireColumn.Hidden = True
            Next
        Case Else
    End Select
Next
Application.ScreenUpdating = True
Hope that helps.
 

SevenOut

New Member
Joined
Jan 7, 2020
Messages
32
Office Version
2013
Platform
Windows
@SevenOut
Assuming there are no blanks other than at the end of months with less than 31 days and, that all columns are visible at the time of running then try...

VBA Code:
Sub HideBlanks()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets

    Select Case LCase(Trim(ws.Name))
        Case "february", "april", june, "september", "november"
            For c = 30 To 32
            If Len(ws.Cells(2, c).Value) = 0 Then ws.Cells(2, c).EntireColumn.Hidden = True
            Next
        Case Else
    End Select
Next
Application.ScreenUpdating = True
Hope that helps.
That worked great. Is there a way to unhide the columns if leap year? I bound the macro to a button to hide blanks. I'd like to do the same if the current hidden column now has a value.
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,735
VBA Code:
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    Select Case LCase(Trim(ws.Name))
        Case "april", "june", "september", "november": ws.[AF2].EntireColumn.Hidden = True
        Case "february"
            ws.[AD:AF].EntireColumn.Hidden = True
            If Len(ws.[AD2].Value) <> 0 Then ws.[AD2].EntireColumn.Hidden = False
    End Select
Next
Application.ScreenUpdating = True
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,563
Office Version
2010
Platform
Windows
Assuming the cells in range B2:AF2 all contain formula with those formulas displaying numbers for only the days that are actually in the month and displaying blanks otherwise, this compact macro should hide those columns where the formulas are displaying blanks...
VBA Code:
Sub HideBlankDayCells()
  Dim WS As Worksheet
  For Each WS In Sheets(Array("February", "April", "June", "September", "November"))
    WS.Range("AD2:AF2").SpecialCells(xlFormulas, xlTextValues).EntireColumn.Hidden = True
  Next
End Sub
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,735
Assuming the cells in range B2:AF2 all contain formula with those formulas displaying numbers for only the days that are actually in the month and displaying blanks otherwise, this compact macro should hide those columns where the formulas are displaying blanks...
VBA Code:
Sub HideBlankDayCells()
  Dim WS As Worksheet
  For Each WS In Sheets(Array("February", "April", "June", "September", "November"))
    WS.Range("AD2:AF2").SpecialCells(xlFormulas, xlTextValues).EntireColumn.Hidden = True
  Next
End Sub
But see post #3.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,563
Office Version
2010
Platform
Windows
But see post #3.
Does that mean the OP is reusing the workbook year-after-year? That is the only way I can think of that February 29th would be hidden and then get a value. Okay, if so...
VBA Code:
Sub HideBlankDayCells()
  Dim WS As Worksheet
  Sheets("February").Columns("AD").Hidden = False
  For Each WS In Sheets(Array("February", "April", "June", "September", "November"))
    WS.Range("AD2:AF2").SpecialCells(xlFormulas, xlTextValues).EntireColumn.Hidden = True
  Next
End Sub
 

Forum statistics

Threads
1,085,181
Messages
5,382,167
Members
401,779
Latest member
Thonor

Some videos you may like

This Week's Hot Topics

Top