Macro to hide column based on header name

[briandhowells]

I'm looking for a macro that checks the title of columns and hides
particular ones. For example, using the bwlo sample sheet, a macro
that would hide the Age and Location columns automatically:

Name Age Sex Location
John 1 M Home
Steve 4 F Work

As a further capability, I was thinking of creating a spreadsheet with
a listing of all of the columns that I would like hidden, and then
reference that in the macro. So, if the header equals a value on the
column hiding list, the macro would hide that column in my actual
spreadsheet.

Any thoughts? Thanks!

 

[RB Smissaert]

Try something like this:

Sub HideCols()

Dim arr
Dim i As Long
Dim n As Long

'setting up array holding column names to hide
With Sheets("Sheet2")
arr = .Range(.Cells(1), .Cells(10, 1))
End With

For i = 20 To 2 step - 1
For n = 1 To UBound(arr)
If Cells(i) = arr(n, 1) Then
Columns(i).EntireColumn.Hidden = True
Exit For
End If
Next
Next

End Sub

Your list of names to hide will be in Sheet2 in cells A1 to A10 in this
example.


RBS

 

[Die_Another_Day]

I have an addin that I use to hide columns, which has a default setting
that can be used. I can email it to you if you would like.

Charles

 

[briandhowells]

Thanks, sorry for my lameness, but could you show how it would look if
all of column A on sheet 2 contained the values?

Thanks!

 

[RB Smissaert]

Change this:
arr = .Range(.Cells(1), .Cells(10, 1))
to this:
arr = .Range(.Cells(1), .Cells(65536, 1))

RBS

 

[SteveC]

Hi,
How would you make this work to hide columns that just contain certain
pieces of text. For example, hide all columns that contain the phrase
"apple" so column with header "blue apple" would be hidden? thanks very
much...

 

[SteveC]

Hi,
How would you make this work to hide columns that just contain certain
pieces of text. For example, hide all columns that contain the phrase
"apple" so column with header "blue apple" would be hidden? thanks very
much...