Display only columns with certain text

AlexCHI

New Member
Joined
Sep 28, 2017
Messages
19
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.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>AA</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;background-color: #FFFFFF;;">AM Attendance</td><td style="text-align: center;border-top: 1px solid black;background-color: #E7E6E6;;">2</td><td style="text-align: center;border-top: 1px solid black;background-color: #E7E6E6;;"></td><td style="text-align: center;border-top: 1px solid black;background-color: #E7E6E6;;"></td><td style="text-align: center;background-color: #E7E6E6;;"></td><td style="text-align: center;background-color: #E7E6E6;;"></td><td style="text-align: center;background-color: #E7E6E6;;"></td><td style="text-align: center;background-color: #E7E6E6;;"></td><td style="text-align: center;background-color: #E7E6E6;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: right;background-color: #FFFFFF;;">PM Attendance</td><td style="text-align: center;background-color: #E7E6E6;;">2</td><td style="text-align: center;background-color: #E7E6E6;;"></td><td style="text-align: center;background-color: #E7E6E6;;"></td><td style="text-align: center;background-color: #E7E6E6;;"></td><td style="text-align: center;background-color: #E7E6E6;;"></td><td style="text-align: center;background-color: #E7E6E6;;"></td><td style="text-align: center;background-color: #E7E6E6;;"></td><td style="text-align: center;background-color: #E7E6E6;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;text-align: right;background-color: #FFFFFF;;">AM & PM Attendance</td><td style="text-align: center;background-color: #E7E6E6;;">1</td><td style="text-align: center;background-color: #E7E6E6;;"></td><td style="text-align: center;background-color: #E7E6E6;;"></td><td style="text-align: center;background-color: #E7E6E6;;"></td><td style="text-align: center;background-color: #E7E6E6;;"></td><td style="text-align: center;background-color: #E7E6E6;;"></td><td style="text-align: center;background-color: #E7E6E6;;"></td><td style="text-align: center;background-color: #E7E6E6;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: center;background-color: #FFFFFF;;">Jan-1</td><td style="font-weight: bold;text-align: center;background-color: #FFFFFF;;">Jan-2</td><td style="font-weight: bold;text-align: center;background-color: #FFFFFF;;">Jan-3</td><td style="font-weight: bold;text-align: center;background-color: #FFFFFF;;">Jan-4</td><td style="font-weight: bold;text-align: center;background-color: #FFFFFF;;">Jan-5</td><td style="font-weight: bold;text-align: center;background-color: #FFFFFF;;">Jan-6</td><td style="font-weight: bold;text-align: center;background-color: #FFFFFF;;">Jan-7</td><td style="font-weight: bold;text-align: center;background-color: #FFFFFF;;">Jan-8</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;text-align: right;;">Days Present</td><td style="text-align: center;;">Sun</td><td style="text-align: center;font-style: italic;background-color: #FFFFFF;;">Mon</td><td style="text-align: center;font-style: italic;background-color: #FFFFFF;;">Tue</td><td style="text-align: center;font-style: italic;background-color: #FFFFFF;;">Wed</td><td style="text-align: center;font-style: italic;background-color: #FFFFFF;;">Thu</td><td style="text-align: center;font-style: italic;background-color: #FFFFFF;;">Fri</td><td style="text-align: center;font-style: italic;background-color: #FFFFFF;;">Sat</td><td style="text-align: center;font-style: italic;background-color: #FFFFFF;;">Sun</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;background-color: #E7E6E6;;">1</td><td style="text-align: center;background-color: #FFFFFF;;">AM & PM</td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;background-color: #E7E6E6;;">1</td><td style="text-align: center;background-color: #FFFFFF;;">PM</td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;background-color: #E7E6E6;;">1</td><td style="text-align: center;background-color: #FFFFFF;;">AM</td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;background-color: #E7E6E6;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td><td style="text-align: center;background-color: #FFFFFF;;"></td></tr></tbody></table><p style="width:6.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Main Screen</p><br /><br />
 
Last edited:

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
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
 
Last edited:

AlexCHI

New Member
Joined
Sep 28, 2017
Messages
19
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,670
Office Version
365
Platform
Windows
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:
Code:
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
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
Re: How to display only columns with certain text

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

Code:
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,670
Office Version
365
Platform
Windows
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:
Rich (BB code):
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
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:
Rich (BB code):
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,644
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,670
Office Version
365
Platform
Windows
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.
 
Last edited:

AlexCHI

New Member
Joined
Sep 28, 2017
Messages
19
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,082
Messages
5,466,537
Members
406,485
Latest member
kaksolver

This Week's Hot Topics

Top