Excel - Hide Columns based on Heading Criteria

[nashlow]

Hi,
I need help in Excel vba to hide columns based on the text headings of individual columns. So if I only need to display cities on the East Coast and hide the rest from a spreadsheet of franchises US wide, how do I manage this?
Appreciate all your assistance.

Nashlow

 

[PHV]

Have a look at advanced filter.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GVF]

Or if you want it in VBA then there are a couple of ways to do it.

If you know which columns contain east coast cities (and those columns always contain east coast cities) then you can simply hide specific columns in a macro:
Columns 10,11,12,18 and 23 contain east coast cities then

Range(Columns(10),Columns(12)).EntireColumn.Hidden = true
Columns(18).EntireColumn.Hidden = true
Columns(23).EntireColumn.Hidden = true

That is the easiest but probably not the best way because if you add a column somewhere and don't update the code, the wrong column will get hidden.

You can get fancy and have a Userform come up and allow the user to check boxes indicating which areas of the country he wants to view. Within the VBA code you would need to include a listing of the cities of each area and probably add them to an array (WestCoastArray(), EastCoastArray() etc.) related to a checkbox. You can then use a "For Next" loop to cycle thru the appropriate arrays and search row 1 for the cities and hide the columns using something like:

On Error Resume Next
Dim WestCoastArray() As String
ReDim WestCoastArray(4)
WestCoastArray(0) = "San Diego"
WestCoastArray(1) = "San Francisco"
WestCoastArray(2) = "Los Angeles"
WestCoastArray(3) = "Oxnard"
For Each CityName In WestCoastArray()
Err.Clear
Rows(1).Select
SResults = Selection.Find(What:=CityName, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
If Err <> 0 Then
MsgBox CityName & " was not found"
GoTo NextValue
End If
Columns(ActiveCell.Column).EntireColumn.Hidden = True
NextValue:
Next CityName

Remember to catch the error that is generated if the ".Find" method fails because your activecell will not be in the column that needs to be hidden.

Don't forget to include a macro to unhide all the columns.
Range(columns(1), Columns(X)).EntireColumn.Hidden = false

Greg

 

[nashlow]

Thanks Greg,
Yuor advice was very helpful and intend to use it.
My specific problem is to scan a row of columns looking for particular cities. If city fits the criteria, its column stay unhidden otherwise hide all.
I am sure I can tune your idea to suit this criteria. I am also looking at the Union method to achieve the same result. The only problem is that the columns are hardly static, so the code needs to be flexible to accomodate these subtle changes without fail.

Nashlow