Hiding Empty Columns

IorekByrnison

New Member
Joined
Jan 20, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hoping somebody can help me out here

I've got an excel sheet where for a specific range of values I want to hide the columns if there is nothing entered into the cells for for that column. The examples I could find to work only worked if you were looking at the entire column, but in my case, the data I want to look at starts in the 3rd for of each column. So F3:F30, G3:G30, and so forth. Data is not just numbers if that matters, could be numbers or letters, but either way, I want the column to hide if there is nothing entered within the range for that column. Thanks for any help.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Here is a VBA procedure that should do what you want.
VBA Code:
Sub HideColumns()

    Dim col As Long
    Dim rng As Range
    
    Application.ScreenUpdating = False
    
'   Run against columns 6-10 ("F:J")
    For col = 6 To 10
'       Set rng to check (rows 3:30)
        Set rng = Range(Cells(3, col), Cells(30, col))
'       Count non-blank cells in range
        If Application.WorksheetFunction.CountA(rng) = 0 Then
'           Hide column if no non-blank cells in range
            Columns(col).Hidden = True
        End If
    Next col
    
    Application.ScreenUpdating = True
    
End Sub
This example is running against columns F-J, but you can easily change that to suit your needs.
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad I was able to help! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,589
Messages
5,625,671
Members
416,125
Latest member
NeedExcelHelp2021

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