Cyril asks how to hide all columns of a range except columns whose header is found in specific cells. I am also going to show you how to hide columns in a table. The table is a data source for a chart.

Table of Contents

  1. Cyrils question
  2. Hiding columns in a table

Oscar,
Do you have a vba to hide all columns of a range except columns whose header is found in specific cells?
eg:
B1 and B2 holds header names chosen by user.
range C1 to P2 holds all headers.
Hide ALL columns except those sharing name found in B1 and B2?

Answer:

VBA code in sheet "Cyril" module

  1. Right click on sheet name
  2. Click "View Code"
  3. Paste code below in module
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Value As Range, Cells As Range, Cell As Variant
Dim c As Integer, b As Boolean
If Not Intersect(Target, Range("B1:B2")) Is Nothing Then
    Application.ScreenUpdating = False
    Worksheets("Cyril").Range("C1:P2").EntireColumn.Hidden = False
    For c = 0 To 13
        Set Cells = Worksheets("Cyril").Range("C1:C2").Offset(0, c)
        b = False
        For Each Cell In Cells
            For Each Value In Worksheets("Cyril").Range("B1:B2")
                If Value = Cell Then
                    b = True
                End If
            Next Value
        Next Cell
        If b = False Then
            Cells.EntireColumn.Hidden = True
        End If
    Next c
    Application.ScreenUpdating = True
End If
End Sub

Hiding columns in a table

VBA code in sheet module

  1. Right click Sheet name
  2. Click "View Code"
  3. Paste vba code below in module
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Integer
If Not Intersect(Target, Range("A15")) Is Nothing Then
    Application.ScreenUpdating = False
    Range("Table1").EntireColumn.Hidden = False
    For c = 2 To Range("Table1").Columns.Count
        If Range("Table1[#All]").Cells(c) <> Range("A15") Then
            Range("Table1").Cells(c).EntireColumn.Hidden = True
        End If
    Next c
    Application.ScreenUpdating = True
End If
End Sub

 

Download excel *.xlsm file

Hide columns.xlsm