Macro to hide columns by Header Names

[zalik22]

Hi,

I am looking to hide columns by header names. The columns I would like to hide are "name", "date" and "age".

Thanks!

 

[wideboydixon]

Generic solution:

Private Const FirstRow = 1 ' The row to check
Public Sub HideNameDateAge()

' Hide "Name", "Date" and "Age" columns
HideColumns True, "Name", "Date", "Age"

End Sub
Public Sub ShowNameDateAge()

' Show "Name" and "Date" columns
HideColumns False, "Name", "Date"

End Sub
Public Sub HideColumns(hideColumn As Boolean, ParamArray columnHeaders() As Variant)

Dim lastCol As Long
Dim thisCol As Long
Dim columnName As Variant

' Find the last column
lastCol = Cells(FirstRow, Columns.Count).End(xlToLeft).Column

' Look at all columns
For thisCol = 1 To lastCol
    ' Look at the names we were passed
    For Each columnName In columnHeaders
        ' Check if it matches
        If Cells(FirstRow, thisCol).Value = columnName Then
            ' Show / Hide as appropriate
            Cells(FirstRow, thisCol).EntireColumn.Hidden = hideColumn
        End If
    Next columnName
Next thisCol

End Sub

WBD

 

[My Aswer Is This]

Try this:

Sub Hide_Me()
'"name", "date" and "age"
Dim Lastcolumn As Long
Lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 1 To Lastcolumn
        If Cells(1, i).Value = "name" Or Cells(1, i).Value = "date" Or Cells(1, i).Value = "age" Then Columns(i).Hidden = True
    Next
End Sub

 

[zalik22]

Great! Thanks so much!

Try this:

Sub Hide_Me()
'"name", "date" and "age"
Dim Lastcolumn As Long
Lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 1 To Lastcolumn
        If Cells(1, i).Value = "name" Or Cells(1, i).Value = "date" Or Cells(1, i).Value = "age" Then Columns(i).Hidden = True
    Next
End Sub

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you
need additional assistance.

Great! Thanks so much!

 

[zalik22]

Is there a way to reference a column, I want to do some formatting with a column header "User ID" in column D. I know
Columns("D:D").Select
works, but was trying to use

Columns("User ID").Select
but getting an error. Thanks!

 

[Fluff]

How about

Sub Fluff15_8()
    
    Dim Col As Long
    Col = Rows(1).Find("User ID").Column
    Columns(Col).Select


End Sub

 

[zalik22]

Getting an error, this is what I have:
Dim Col As Long
Col = Rows(1).Find("User ID").Column
Columns(Col).Select
Selection.ColumnWidth = 75
Selection.WrapText = True

 

[Fluff]

What error are you getting & what line does it highlight if you click debug?

 

[gallen]

You can but you need to create a table (Select your table then INSERT>TABLE) and give it a name (default is Table1) in the 'DESIGN' tab that pops up when you select any cell within your table

So for example if I have a table named Table1 and in it I have a column titled "Name" I can reference it like this:

Range("Table1[Name]").EntireColumn.Hidden = True