Hide Columns based on value: List of Criteria

[Sandcastor]

I Have:
A table that I use as a log sheet which currently has 29 columns (I sometimes add more), and over 500 rows (adding more all the time).
It is used to log samples that our clients bring in. The first few column headers are like so:
Company, Location, Job ID, Sample Numbers, Sample date Range, Date Received, Date shipped out, Tracking #, Samples Billed, Samples remain
I use the log for a few different functions like collecting information for various reports, and for guiding me in when to discard samples and such. To make these jobs easier, I hide certain groups of columns. Macros like the following help with that:

Sub UploadMode()'
Application.ScreenUpdating = False
    Range("E:F,J:L,Q:S").EntireColumn.Hidden = True
Application.ScreenUpdating = True
    
End Sub

This works, but I occasionally add a column or take one out.
I could do a FOR EACH NEXT to iterate through each cell and test each value individually, but that means 29^2 checks. Very slow and hard to update with new criteria.
I need something to check the value of a cell, compare it to a list, and hide if true. Plain-language code:

Define a list of values based on some of the header values
FOR EACH cell in range.(“Headers”)
Check cell value against the list and if true then EntireColumn.Hidden = True
NEXT

Any Ideas?

 

[Trebor76]

If you create a separate list with column headers in one column and a yes/no column next to it you could hide the columns based on this setting by looping through the headers backwards from the last column to the first doing a VLOOKUP to yes/no field which would hide/show that column respectively.

 

[Sandcastor]

I appreciate the effort, but that’s fairly wide of the mark. Going in and manually changing cell values each time I want to macro which columns to hide will actually take even more time than simply hiding columns. And far more time than I’m asking for. What I need is a way to define a list of values for each IF THEN to check against.

 

[Trebor76]

Not sure what you mean

You only need need to define the list once. The macro would do the lookup and hide the column(s) accordingly.

 

[Sandcastor]

I don’t want to do it that way.
I don’t want helper cells for the macro to reference.
It amounts to no difference to what I have already.

What I’m looking for, put as simply as I can, is to have the IF THEN reference a hard-coded list of values. Refer to my original post. It’s a thing I know can be done, I have seen it before, but haven’t learned yet. I’m not really interested in “but do it this other roundabout way” solutions.

 

[My Aswer Is This]

I'm a little new to this thread.

So if I understand you want to:
1. Search row(1)
2. If a value is found that you have hard coded then you want that column Hidden.
Is that what you want?

And you said:

reference a hard-coded list of values.

So this hard coded list would be in the script it would not be on a sheet someplace.

OK so give me a few of those hard coded values​

So if George is a hard coded value and George in in Range("G1") then column G would be hidden
is that what you want?

 

[Trebor76]

Try this from the sheet in question. Just change the names in the varMyHeaderTitles array to suit:

Option Explicit
Sub Macro1()

    Dim varMyHeaderTitles As Variant
    Dim lngLastCol As Long
    Dim lngMyCol As Long
    
    Application.ScreenUpdating = False
    
    varMyHeaderTitles = Array("Red", "Green", "Blue") 'List of header names NOT to be hidden (not case sensitive). Change to suit.
    
    lngLastCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    For lngMyCol = lngLastCol To 1 Step -1
        'If the header text is not part of the array defined above, then...
        If IsNumeric(Application.Match(Cells(1, lngMyCol), varMyHeaderTitles, 0)) = False Then
            '...hide that column
            MsgBox Cells(1, lngMyCol)
            Columns(lngMyCol).EntireColumn.Hidden = True
        'Else...
        Else
            '...make that column visible
            Columns(lngMyCol).EntireColumn.Hidden = False
        End If
    Next lngMyCol
    
    Application.ScreenUpdating = True

End Sub

 

[My Aswer Is This]

If the answer to my question is yes then try this:

Add more values as you want.
You will see I added six.
Modify to your needs. These are the values to look for in Row(1)

Sub Hide_Columns()
'Modified  11/9/2018  10:22:20 PM  EST
Dim i As Long
Dim Lastcolumn As Long
Lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
Dim r As Range
For Each r In Cells(1, 1).Resize(, Lastcolumn)
    With r.Value
        Select Case r.Value
            Case "Dad", "Mom", "Bob", "George", "Stanley", "Julia"
                r.Columns.Hidden = True
            Case Else
            r.Columns.Hidden = False
        End Select
    End With
Next
End Sub

 

[Sandcastor]

All set now!

Trebor76, the line

 varMyHeaderTitles = Array("Red", "Green", "Blue")

was what I had been looking for to do the comparisons I needed. I knew I could reference and array, but didn’t know how. Thanks.

“My Aswer Is This”, I will for sure remember the snippet for other dynamically sized arrays:

 Lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
For Each r In Cells(1, 1).Resize(, Lastcolumn)

Ultimately, I liked how Cases work as another way to reference an array. I combined, compiled, and ended up with this:

Sub DumpMode()
Dim r As Range
For Each r In Cells.Range("LogHeaders") ‘Named Range includes all headers in table
    With r.Value
        Select Case r.Value
            Case "Customer", "Location", "Job ID", "Job Type", "Sample # Received", "Date Range", "Shipped Date", "Stored", "Dump"
                r.Columns.Hidden = False
            Case Else
                r.Columns.Hidden = True
        End Select
    End With
Next
End Sub

I also have three other view modes with other column headers listed in the cases, for when I have other uses for the table.

Thanks so much for your help, friends!

 

[My Aswer Is This]

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