Hide Column if Range is Blank

leeksleeks

Board Regular
Joined
Oct 31, 2013
Messages
95
Hi,

I am wanting to hide a column if there is no text in a range. My range is B4:AA27 and I want it to treat it on a column by column basis (B4:B27 and then C4:C27 etc). I have found a few variations online but nothing that I have got to work so far.

Thanks!
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,005
This does exactly as you ask though could be made more flexible:

Code:
Sub HideBlankColumns()

    Dim iFirstCol As Integer, iLastCol As Integer, i As Integer
    
    'variables to hold the first and last column numbers
    iFirstCol = Range("B4").Column
    iLastCol = Range("AA4").Column
    
    
    'count backwards through columns
    For i = iLastCol To iFirstCol Step -1
        'if all cells are blank, hide the column
        If WorksheetFunction.CountA(Range(Cells(4, i), Cells(27, i))) = 0 Then
           Columns(i).EntireColumn.Hidden = True
        End If
    Next i
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,005
Messages
5,526,233
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top