Excel 2010 Macro for Creating a button that hides all blank columns in a range

Question

I'm new with writing Macros and I'm completely stumped as to how to get my command button to only hide and unhide those columns that are blank within my range.

I've got my command button in place and macro written for hiding and unhiding all columns so far and that is working perfectly to hide all columns. But I don't know how to incorporate an argument so that as my users enter data onto the spreadsheet, it will only hide/unhide the blank columns and not any column that has any data entry in it.

My Hide/Unhide Command Button code is entered as such. Nothing will be entered past Column "CW". First data will be entered on Column "B".

Private Sub HideColumnsButton_Click()

     If HideColumnsButton.Caption = "Hide Blank Columns" Then
        Columns("B:CW").Select
        Selection.EntireColumn.Hidden = True
        HideColumnsButton.Caption = "UnHide Blank Columns"
    Else
        Columns("B:CW").Select
        Selection.EntireColumn.Hidden = False
        HideColumnsButton.Caption = "Hide Blank Columns"
    End If

End Sub

Unknown Unknown · Accepted Answer · 2014-02-27T17:51:13

This should help steer you in the right direction. To hide columns:

Application.ScreenUpdating = False
Dim rng As Range
For Each rng In Range("A1:CW1")
    If WorksheetFunction.CountA(rng.EntireColumn) = 0 Then
        rng.EntireColumn.Hidden = True
    End If
Next rng
Application.ScreenUpdating = True

Excel 2010 Macro for Creating a button that hides all blank columns in a range

1 Answers