Hide columns with no count

[xdriver]

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.

 

[alansidman]

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]

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_MH	DIN_LEVEL_RN	DIN_LEVEL_SF	DIRECTION	DISCLOSURES	DIVIDABLE_CI	DOCUMENTS_ON_FILE_BU	DOCUMENTS_ON_FILE_CC	DOCUMENTS_ON_FILE_LD	MARKET_TIME	MARKET_TIME_BROKER	MARKET_TIME_PROPERTY	DPR_Flag	DAYS_TO_OFFER	EASEMENTS_CI	ELECTRIC_FEATURE_CC	ELECTRIC_FEATURE_LD	ELECTRIC_FEATURE_MF
			Common St to Morse St						17	17	17	N	3
			Natick Center (Rt 27)						4	4	4	N	5
			Church St to Common St						5	5	5	N	5
			Rt 135 to Church St						16	16	16	N	16
			Common St to Morse St						4	4	4	N	2
			Church St to Common St to Morse St						2	2	2	N
			Rt 135 to Church St						14	14	14	N	14
			Rt 135 to Church St						0	0	0	N
			Washington Ave to Summer Street						4	4	4	N	4
			Off East central street.						28	28	28	N	28
			West Central St (Rt 135) west first property after strip center						11	11	11	N	11
			Natick Village, off 135.	Agent is owner.					2	2	2	N	1
			W. Central to High ST	Tenant 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.					3	3	3	N	3
			Rte 135 to Kendall Drive to Village Rock Lane	Landlord will pay Hammond rental fee. Cooperating broker must get their fee from his/her client.					19	19	19	N	19
			Rte 135 (West Central) to Kendall Street to Village Hill Lane						15	15	15	N	15
			Corner of Sherman St and Morse St						27	27	27	N	27
			Off Rte 135	Places Real Estate application procedure					14	14	14	N	14
			Corner of Grant St and East Central St						15	15	15	N	15
			North Ave. to Walnut St #39 on right just after Willow St.						7	7	7	N	7
			@ 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.					10	10	10	N	10
			North Avenue to Florence Street top of hill						30	30	30	N	30
			Squire at Newfield						11	11	11	N	11
			Rte 135 to Newfield	Places Real Estate application procedure required					34	34	34	N	34
			Newfield Dr to Walden Dr to Squire Ct						9	9	9	N	7
	1		rte 135 to kendall ave/keys: first right off kendal/go to where all lockboxes are for natick village	NATICK VILLAGE CONDO ASSOCIATION HAS VOTED THE ENTIRE NATICK VILLAGE SPACE A NO SMOKING PROPERTY(INSIDE UNITS ALSO)					23	23	23	N	23
			Natick Green Condo						3	3	3	N	3
			Rte 135 (West Central) to Kendall Street to Village Hill Lane	No Smoking Complex					4	4	4	N	0
			West Central St (Rt 135) to Newfield Drive to Thoreau Ct.						7	7	7	N	7
			Route 135 (West Central) to Kendall Street to Village Way						8	8	8	N	8
			Rt. 135 to Kendall Lane to Village Hill Lane. Look for Elliot Hill entrance	SHOWINGS START OCT 15 BETWEEN 12-2PM					22	22	22	N	22

 

[alansidman]

Here is a VBA solution for you

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]

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]

Try changing this line

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

to

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

 

[xdriver]

It didn't hide any columns.

 

[alansidman]

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]

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.

 

[alansidman]

double checking that.