Hide columns where cells in a range are blank

mkopenhagen · Jun 28th 2021

I have every day of the entire year on the columns, but there isn't necessarily an entry in any of the rows for every day. I would like to hide the entire column (day) if that (all other rows of that columns are empty) is true.

I did find a thread about this, but the macro did not work. See link below:

**royUK** · Jun 28th 2021

Attach an example workbook

**KjBox** · Jun 28th 2021

Try this

Code

Option Explicit

Sub HideColumns()
    Dim i As Long
    
    With Sheets("Sheet1").UsedRange 'Change sheet name to suit
        .Columns.Hidden = 0
        For i = 1 To .Columns.Count
            If Application.CountA(.Columns(i)) = 1 Then 'Change to "= 0" if no header row
                .Columns(i).Hidden = 1
            End If
        Next
    End With
    
End Sub

Display More

tr1face · Jun 29th 2021

Code

Sub HideBlankColumns()
' Defines variables    Dim rng As Range, rcell As Range
' Defines range to check    Set rng = Range("A:A")
' For each cell in range    For Each rcell In rng
' If cell value is blank then...        If rcell.Value = "" Then
' It's column is hidden            rcell.EntireColumn.Hidden = True        End If
' Check next cell in range    Next rcell
End Sub

Hide columns where cells in a range are blank

Participate now!

Share

Tags