Hide columns with no count

xdriver

New Member
Joined
Mar 21, 2014
Messages
43
I have a worksheet that I would like to hide all columns that have a zero count since some cells contain numerical data and some contain text. All columns have a header, so those should be excluded in the count. I read this link Macro to Hide Columns with ZERO value - Mr. Excel but I think it's not doing what I would like because changing the count to countA includes the header row, and every column has header text. Thank you.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,184
Office Version
  1. 365
Platform
  1. Windows
Show us some sample data to work with. Use XL2BB to upload the sample and also show a mocked up expected result for the sample data.
 

xdriver

New Member
Joined
Mar 21, 2014
Messages
43
Show us some sample data to work with. Use XL2BB to upload the sample and also show a mocked up expected result for the sample data.
Here is a small sample, but there are tons of columns going all the way out to column AHO. You can see that some columns have numerical data, some have text, and some have nothing other than the header. I want to hide columns, including hiding of header, that have nothing in them. Said a different way, hide the column if there is nothing in the column other than the header itself.

DIN_LEVEL_MHDIN_LEVEL_RNDIN_LEVEL_SFDIRECTIONDISCLOSURESDIVIDABLE_CIDOCUMENTS_ON_FILE_BUDOCUMENTS_ON_FILE_CCDOCUMENTS_ON_FILE_LDMARKET_TIMEMARKET_TIME_BROKERMARKET_TIME_PROPERTYDPR_FlagDAYS_TO_OFFEREASEMENTS_CIELECTRIC_FEATURE_CCELECTRIC_FEATURE_LDELECTRIC_FEATURE_MF
Common St to Morse St171717N3
Natick Center (Rt 27)444N5
Church St to Common St555N5
Rt 135 to Church St161616N16
Common St to Morse St444N2
Church St to Common St to Morse St222N
Rt 135 to Church St141414N14
Rt 135 to Church St000N
Washington Ave to Summer Street444N4
Off East central street.282828N28
West Central St (Rt 135) west first property after strip center111111N11
Natick Village, off 135.Agent is owner.222N1
W. Central to High STTenant responsible for broker fee of one month rent. New carpet going in BR Thanksgiving week. Coin op Washer/Dryer. No Pets. No smoking. Owner contributes to Electric bill $80 per month.333N3
Rte 135 to Kendall Drive to Village Rock LaneLandlord will pay Hammond rental fee. Cooperating broker must get their fee from his/her client.191919N19
Rte 135 (West Central) to Kendall Street to Village Hill Lane151515N15
Corner of Sherman St and Morse St272727N27
Off Rte 135Places Real Estate application procedure141414N14
Corner of Grant St and East Central St151515N15
North Ave. to Walnut St #39 on right just after Willow St.777N7
@ intersection of Rte. 16, across the street from the South Natick waterfalls (see GoogleMaps/GPS)1st mo rent+sec dep due @signing;1 mo broker fee due@appl: tenant to pay 1 mo fee*; spilt 50/50/ if agent accompanies; 25% referral fee if agent sending customer. $35 appl fee/background check for ea. tenant over 18 yrs old. Last mo's rent may be req. dep on credit.101010N10
North Avenue to Florence Street top of hill303030N30
Squire at Newfield111111N11
Rte 135 to NewfieldPlaces Real Estate application procedure required343434N34
Newfield Dr to Walden Dr to Squire Ct999N7
1rte 135 to kendall ave/keys: first right off kendal/go to where all lockboxes are for natick villageNATICK VILLAGE CONDO ASSOCIATION HAS VOTED THE ENTIRE NATICK VILLAGE SPACE A NO SMOKING PROPERTY(INSIDE UNITS ALSO)232323N23
Natick Green Condo333N3
Rte 135 (West Central) to Kendall Street to Village Hill LaneNo Smoking Complex444N0
West Central St (Rt 135) to Newfield Drive to Thoreau Ct.777N7
Route 135 (West Central) to Kendall Street to Village Way888N8
Rt. 135 to Kendall Lane to Village Hill Lane. Look for Elliot Hill entranceSHOWINGS START OCT 15 BETWEEN 12-2PM222222N22
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,184
Office Version
  1. 365
Platform
  1. Windows
Here is a VBA solution for you

VBA Code:
Option Explicit

Sub HideMT()
    Dim lr As Long, lc As Long
    Dim i As Long
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 1 To lc
        lr = Cells(Rows.Count, i).End(xlUp).Row
        If WorksheetFunction.Count(Range(Cells(2, i), Cells(lr, i))) = 0 Then
            Cells(1, i).EntireColumn.Hidden = True
        End If
    Next i
End Sub


Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button
 

xdriver

New Member
Joined
Mar 21, 2014
Messages
43

ADVERTISEMENT

Thank you. That appears to have worked except it also hides columns that only contain text. For instance, the column in my example that has the header "DPR_Flag" is now hidden even though "N" is in the cells below the header. Is there a way to modify what you created to only hide columns that have nothing in any cell somewhere below the header. Thank you.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,184
Office Version
  1. 365
Platform
  1. Windows
Try changing this line

VBA Code:
If WorksheetFunction.Count(Range(Cells(2, i), Cells(lr, i))) = 0 Then

to

VBA Code:
If WorksheetFunction.CountA(Range(Cells(2, i), Cells(lr, i))) = 0 Then
 

xdriver

New Member
Joined
Mar 21, 2014
Messages
43

ADVERTISEMENT

It didn't hide any columns.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,184
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
Option Explicit

Sub HideMT()
    Dim lr As Long, lc As Long
    Dim i As Long
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 1 To lc
        lr = Cells(Rows.Count, i).End(xlUp).Row
        If WorksheetFunction.CountBlank(Range(Cells(2, i), Cells(lr, i))) <> 0 Then
            Cells(1, i).EntireColumn.Hidden = True
        End If
    Next i
End Sub
 

xdriver

New Member
Joined
Mar 21, 2014
Messages
43
Unfortunately, this hides columns such as the example table with a header of "DIN_Level_RN" where it has a value lower in the rows.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,381
Messages
5,635,924
Members
416,887
Latest member
SheriE

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