asked on

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.

Santosh Gupta

i not clear what do you want... but it seems you can achieve your goal by Filter option.

Ravi Vaddadi

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

Minot

ASKER

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

Minot

ASKER

getting a type mismatch on

If (InStr(colsToHide, cellR, vbTextCompare) > 0) Then

ASKER CERTIFIED SOLUTION

Ravi Vaddadi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ravi Vaddadi

Remove vbTextCompare

Minot

ASKER

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.

Ravi Vaddadi

You have add like "[phone]". Also what is the error you are getting?

Minot

ASKER

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.

Minot

ASKER

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

Minot

ASKER

Thank you for the help, this solution works well.

Ravi Vaddadi

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