Display only columns with certain text

[AlexCHI]

Hi all,

Please teach me how I can display only columns with certain text in Excel 2007? There are a lot of help on filtering rows but I am hoping to create either a toggle button or set up a reference cell (say "B4") along with a VBA to display columns with selected days only.

For example, method #1 : the user click the toggle button labeled "Sunday" or method #2 : the user inputs "Sunday" in "B4". Then only column T and column AA and all the Sunday columns will show.

Thank you for any help you can think of.

Excel 2007
	S	T	U	V	W	X	Y	Z	AA
6	AM Attendance	2
7	PM Attendance	2
8	AM & PM Attendance	1
9		Jan-1	Jan-2	Jan-3	Jan-4	Jan-5	Jan-6	Jan-7	Jan-8
10	Days Present	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun
11	1	AM & PM
12	1	PM
13	1	AM
14
Main Screen

 

[My Aswer Is This]

Re: How to display only columns with certain text

In this example we input "Sun" only columns "T" an "AA" would be visible.
And the Day "Sun" will always be in row (10)

So in this example we would not see column "S" because there is no "Sun" in row(10) of column "S"
Is this correct? If not please give more details

 

[AlexCHI]

Re: How to display only columns with certain text

Hi My Aswer Is This,

Thank you for your reply. In response to your post, please refer to the following for clarification:

1. Yes, the days (Mon, Tue, Wed, Thu, Fri, Sat, Sun) will always be in row 10
2. Actually, I would like to keep column A:S intact without being impacted by the VBA filter.
3. The range to apply the VBA filter to show/hide the specified columns (either through the method of clicking the toggle button or inputting the day in cell B4) is column T:NT.

Hope the above makes sense... Thank you in advance for your help!

 

[JackDanIce]

Re: How to display only columns with certain text

With #2 , in cell B4 is your input "Sun" or "Sunday"? The following works for either short hand day name or full name day name (i.e. 3 letters or full word) entered into B4:

Sub HideCols()

    Dim rngB4   As Range
    Dim rng     As Range
    Dim LC      As Long
    Dim y       As Long
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
        Set rngB4 = .Cells(4, 2)
        LC = .Cells(10, .Columns.count).End(xlToLeft).column
        .Cells(10, 20).Resize(, LC - 19).EntireColumn.Hidden = False
        If Len(rngB4.Value) > 0 And InStr("sunday,monday,tuesday,wednesday,thursday,friday,saturday", LCase$(rngB4.Value)) > 0 Then
            For y = 20 To LC
                If .Cells(10, y).Value = rngB4.Value Or Left$(rngB4.Value, 3) = .Cells(10, y).Value Then
                    If Not rng Is Nothing Then
                        Set rng = Union(rng, .Cells(10, y))
                    Else
                        Set rng = .Cells(10, y)
                    End If
                End If
            Next y
        End If
        
        If Not rng Is Nothing Then
            rng.EntireColumn.Hidden = True
            Set rng = Nothing
        End If
        
        Set rngB4 = Nothing
    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[My Aswer Is This]

Re: How to display only columns with certain text

Try this:
Will look in Range "B4" for day

Sub Hide_Columns()
Application.ScreenUpdating = False
Dim i As Long
Dim Choice As String
Choice = Range("B4").Value
Columns.Hidden = False
    For i = 384 To 20 Step -1
        If Cells(10, i).Value <> Choice Then
        
        Columns(i).Hidden = True
        Else: Columns(i).Hidden = False
        End If
    Next
Application.ScreenUpdating = True
MsgBox "Done"
End Sub

 

[JackDanIce]

Re: How to display only columns with certain text

@M.I.A.T There's ambiguity whether the input value in B4 is the full name of the day, versus the screenshot which shows T10:AA10 with shortened names, however, thanks to your code, spotted code I suggested was doing the opposite of the request, correction highlighted and assumption is to check for 3 letter day name:

Sub HideCols()


    Dim rngB4   As Range
    Dim rng     As Range
    Dim LC      As Long
    Dim y       As Long
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
        Set rngB4 = .Cells(4, 2)
        LC = .Cells(10, .Columns.count).End(xlToLeft).column
        .Cells(10, 20).Resize(, LC - 19).EntireColumn.Hidden = False
        If Len(rngB4.Value) > 0 And InStr("sunday,monday,tuesday,wednesday,thursday,friday,saturday", LCase$(rngB4.Value)) > 0 Then
            For y = 20 To LC
                If Left$(rngB4.Value, 3) <> .Cells(10, y).Value Then
                    If Not rng Is Nothing Then
                        Set rng = Union(rng, .Cells(10, y))
                    Else
                        Set rng = .Cells(10, y)
                    End If
                End If
            Next y
        End If
        
        If Not rng Is Nothing Then
            rng.EntireColumn.Hidden = True
            Set rng = Nothing
        End If
        
        Set rngB4 = Nothing
    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[My Aswer Is This]

Re: How to display only columns with certain text

The User should put a Data validation list in Range("B4") and in in Row(10). This way there is no ambiguity. Thanks.

@M.I.A.T There's ambiguity whether the input value in B4 is the full name of the day, versus the screenshot which shows T10:AA10 with shortened names, however, thanks to your code, spotted code I suggested was doing the opposite of the request, correction highlighted and assumption is to check for 3 letter day name:

Sub HideCols()


    Dim rngB4   As Range
    Dim rng     As Range
    Dim LC      As Long
    Dim y       As Long
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
        Set rngB4 = .Cells(4, 2)
        LC = .Cells(10, .Columns.count).End(xlToLeft).column
        .Cells(10, 20).Resize(, LC - 19).EntireColumn.Hidden = False
        If Len(rngB4.Value) > 0 And InStr("sunday,monday,tuesday,wednesday,thursday,friday,saturday", LCase$(rngB4.Value)) > 0 Then
            For y = 20 To LC
                If Left$(rngB4.Value, 3) <> .Cells(10, y).Value Then
                    If Not rng Is Nothing Then
                        Set rng = Union(rng, .Cells(10, y))
                    Else
                        Set rng = .Cells(10, y)
                    End If
                End If
            Next y
        End If
        
        If Not rng Is Nothing Then
            rng.EntireColumn.Hidden = True
            Set rng = Nothing
        End If
        
        Set rngB4 = Nothing
    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[Peter_SSs]

Re: How to display only columns with certain text

Alex, can you confirm exactly what is in row 10 from column T onward?
Is it ..
- Directly entered text "Sun", "Mon" etc, or
- Formula returning text eg =TEXT(T9,"ddd"), or
- Formula returning a date but then custom formatted to show the day name. eg =T9 with custom format of "ddd", or
- Directly entered date with custom format eg 1-Jan-2017 with custom format of "ddd"

 

[JackDanIce]

Re: How to display only columns with certain text

M.I.A.T

The User should put a Data validation list in Range("B4") and in in Row(10). This way there is no ambiguity. Thanks.

This hasn't been stated anywhere either by OP or yourself.

or method #2 : the user inputs "Sunday" in "B4". Then only column T and column AA and all the Sunday columns will show.

The screen shot, clearly shows values of Sun, Mon, Tue etc, to me, this is NOT the same as Sunday in B4 hence both @Peter_SSs and myself checking clarification on this. Thanks.

 

[AlexCHI]

Re: How to display only columns with certain text

Hi Peter and Jack,

My apologies of the ambiguity. Row 10 from column T onward is formula returning text, e.g. =TEXT(T9,"ddd").

Jack, really appreciate the two input options in B4! If possible, would like to keep both short hand and full name day names as inputted options for the user.

Much appreciated for all your help!