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

 

[JackDanIce]

Re: How to display only columns with certain text

Hi Alex, code in #6 reduces B4 to 3 letters and then checks T20:NT20, so long as Sunday = sun, Monday = Mon ... Saturday = Sat

Code that checks:

If Left$(rngB4.Value, 3) <> .Cells(10, y).Value Then

So it shouldn't matter if B4 contains either full name or 3-letter as input.

Test M.A.I.T's code in #5 or corrected one in #6. I assume you haven't used data validation anywhere either.

 

[Peter_SSs]

Re: How to display only columns with certain text

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

Try this in a copy of your workbook. Columns should hide/unhide automatically as you enter/delete values (full or short) in B4. I have assumed that dates are continuous by day across row 10.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim sDay As String
  Dim rFound As Range
  Dim c As Long
  
  If Not Intersect(Target, Range("B4")) Is Nothing Then
    Application.ScreenUpdating = False
    sDay = Left(Range("B4").Value, 3)
    Columns("T:NT").Hidden = False
    If Len(sDay) > 0 Then
      Set rFound = Range("S10:NT10").Find(What:=sDay, After:=Range("S10"), LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchDirection:=xlNext, SearchFormat:=False)
      If Not rFound Is Nothing Then
        Columns("T:NT").Hidden = True
        For c = rFound.Column To Columns("NT").Column Step 7
          Columns(c).Hidden = False
        Next c
      End If
    End If
    Application.ScreenUpdating = True
  End If
End Sub