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!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,006
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,132,875
Messages
5,655,754
Members
418,234
Latest member
jdorfma

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
Top