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

SevenOut

Board Regular
Joined
Jan 7, 2020
Messages
82
Office Version
  1. 2013
Platform
  1. 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).
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
@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.
 
Upvote 0
@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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top