Excel Macro - Hide Columns based header row

Hi,

I have various spreadsheets which have somewhere between 100 and 200 columns and many rows containing event data. I need to come up with a way to hide all columns which do not match criteria in row 1. Example

Columns
First_name
Last_name
Sex
Age
so on and so forth

I am looking to hide all but First_name and Last_name for the sake of the example.
MinotAsked:
Who is Participating?
 
SriVaddadiConnect With a Mentor Commented:
Attached file
Copy-of-Book1.xlsm
0
 
Santosh GuptaCommented:
i not clear what do you want... but it seems you can achieve your goal by Filter option.
0
 
SriVaddadiCommented:
Sub HideColumns()

Dim currentSheet As Worksheet
Dim totalSheets As Integer
Dim totalCols As Integer
Dim colsToHide As String
Dim cellR As Range

colsToHide = "First_Name,Last_Name"

totalSheets = ThisWorkbook.Sheets.Count




For i = 1 To totalSheets

    Set currentSheet = ThisWorkbook.Sheets(i)
    totalCols = currentSheet.Columns.Count

    For colIndex = 1 To totalCols
   
        Set cellR = currentSheet.Cells(1, colIndex)
                       
        If (InStr(colsToHide, cellR, vbTextCompare) > 0) Then
                       
            cellR.EntireColumn.Hidden = True
           
       
        End If
       
       
   
    Next colIndex

Next i


End Sub
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
MinotAuthor Commented:
This needs to be in a macro so it can be played against multiple files, doing this as a first step to format data for printing, I want to hide requisite columns then, sort by last name, set page width to 1 page, landscape and print etc. I have all but hiding the columns. I will attach a xls showing the current columns. I want to hide all but a select few, I highlighted them in yellow.
Book1.xlsx
0
 
MinotAuthor Commented:
getting a type mismatch on

  If (InStr(colsToHide, cellR, vbTextCompare) > 0) Then
0
 
SriVaddadiCommented:
Remove vbTextCompare
0
 
MinotAuthor Commented:
still cannot get it to run, removed the compare. Also took your sheet, added Phone to the mix and ran again, did not hide phone.  Truly I am trying to hide all but first, last, etc.
0
 
SriVaddadiCommented:
You have add like  "[phone]". Also what is the error you are getting?
0
 
MinotAuthor Commented:
I think the issue is that my macros are in personal.xlsb thus they are playing on personal not the active sheet.  reviewing that now.
0
 
MinotAuthor Commented:
ok progress, this seems to work

Sub HideColumns()
Dim currentSheet As Worksheet
Dim totalSheets As Integer
Dim totalCols As Integer
Dim colsToHide As String
Dim cellR As Range

colsToHide = "[First_Name],[Last_Name],[Email Address],[Phone],[Phone Area Code]"

totalSheets = ThisWorkbook.Sheets.Count




For i = 1 To totalSheets

    Set currentSheet = ActiveWorkbook.ActiveSheet
    totalCols = currentSheet.Columns.Count

    For colIndex = 1 To totalCols
   
        Set cellR = currentSheet.Cells(1, colIndex)
       
        If (cellR = "") Then
            Exit Sub
        End If
                       
        If (InStr(colsToHide, "[" + cellR + "]") > 0) Then
                       
            cellR.EntireColumn.Hidden = False
        Else
            cellR.EntireColumn.Hidden = True
           
       
        End If
       
       
   
    Next colIndex

Next i


End Sub
0
 
MinotAuthor Commented:
Thank you for the help, this solution works well.
0
 
SriVaddadiCommented:
If you are just doing it for active sheet (just one sheet) then you do not need the sheets for loop.

Set currentSheet = ActiveWorkbook.ActiveSheet
    totalCols = currentSheet.Columns.Count

    For colIndex = 1 To totalCols
   
        Set cellR = currentSheet.Cells(1, colIndex)
       
        If (cellR = "") Then
            Exit Sub
        End If
                       
        If (InStr(colsToHide, "[" + cellR + "]") > 0) Then
                       
            cellR.EntireColumn.Hidden = False
        Else
            cellR.EntireColumn.Hidden = True
           
       
        End If
       
       
   
    Next colIndex
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.