Excel: Hide or unhide columns

In Excel, there is no easy way to unhide just one column. Instead you would need to unhide all the columns then rehide anything you don't want.

Here is an Excel UserForm and accompanying VBA macros that fills this gap.

This macro will hide/unhide columns for a Table or an AutoFilter on the active sheet.

Module: UnhideAColumn

Sub unHideShowColumns()

    UnhideAColumnForm.UserForm_Initialize

    UnhideAColumnForm.Show

End Sub

Userform: UnhideAColumnForm

' Edward Chan 2023

Sub UserForm_Initialize()

    Dim originalRng As range

    Set originalRng = Application.Selection

    Dim headerRange As range

    Set headerRange = getHeaderRange()

    Dim headers() As String

    Dim cell As range

    x = 0

    ReDim Preserve headers(x)

    For Each cell In headerRange.Cells

        ReDim Preserve headers(0 To x)

        headers(x) = headerRange.Cells(1, x + 1).Value

        x = x + 1

    Next

    ListBox1.List = headers

    ' Now select columns that are visible

    i = 0

    For Each cell In headerRange.Cells

        If headerRange.Cells(i + 1).EntireColumn.Hidden = False Then

            ListBox1.Selected(i) = True

        Else

            ListBox1.Selected(i) = False

        End If

        i = i + 1

    Next

End Sub

Private Sub CommandButtonAll_Click()

    For i = 0 To UBound(ListBox1.List)

        ListBox1.Selected(i) = True

    Next

End Sub

Private Sub CommandButtonOK_Click()

    Dim originalRng As range

    Set originalRng = Application.Selection

    Dim headerRange As range

'    Set headerRange = originalRng.ListObject.HeaderRowRange

    Set headerRange = getHeaderRange()

    Dim selectedRows As Collection

    Set selectedRows = GetSelectedRows()

    ' Print the selected rows numbers to the Immediate Window

    ' Hide all columns first

    For Each cell In headerRange.Cells

        headerRange.EntireColumn.Hidden = True

    Next

    For Each Row In selectedRows

        ' Print to the Immediate Window Ctrl + G

        Debug.Print Row

        Debug.Print headerRange.Cells(1, Row + 1).Value

        headerRange.Cells(1, Row + 1).EntireColumn.Hidden = False

    Next Row

    UnhideAColumnForm.Hide

 End Sub

 ' Returns a collection of all the selected items

 Function GetSelectedRows() As Collection

    ' Create the collection

    Dim coll As New Collection

    ' Read through each item in the listbox

    Dim i As Long

    For i = 0 To ListBox1.ListCount - 1

        ' Check if item at position i is selected

        If ListBox1.Selected(i) Then

            coll.Add i

        End If

    Next i

    Set GetSelectedRows = coll

End Function

Private Sub CommandButtonCancel_Click()

    UnhideAColumnForm.Hide

End Sub

Function getHeaderRange() As range

    Dim originalRng As range

    Set originalRng = Application.Selection

    Dim headerRange As range

    Set originalRng = Application.Selection

    If (originalRng.ListObject Is Nothing) Then ' Selection not in a table

        If ActiveSheet.ListObjects.Count > 0 Then ' Try to find first table

            Set headerRange = originalRng.ListObjects(1).HeaderRowRange

        Else ' There are no tables so just use the filter row

            If ActiveSheet.AutoFilterMode = True Then

                Set originalRng = ActiveSheet.AutoFilter.range

            Else

                Exit Function

            End If

            originalRng.Select

            Set headerRange = originalRng.Rows(1)

            headerRange.Select

        End If

    Else

        Set headerRange = originalRng.ListObject.HeaderRowRange

    End If

    Set getHeaderRange = headerRange

End Function