Excel automatically hiding certain columns

[TanyaD]

Hi,

Hoping you can help me. I am completely new to VBA but definitely willing to give it a try and I suspect it may be the only option to do what I need.

I have a large detailed calendar schedule for seven staff (each day has a column for each staff member). I'm hoping I can include a button for each staff member so when I press that button it only shows the columns for that staff member.

I'm guessing that to only show for staff1 the code would say something along the lines of if row 2 (the row that staff names are in) is "staff2" or "staff3" or "staff4" or "staff5" or "staff6" or "staff7" then hide. Which would then only leave Staff1's columns showing?

Greatly appreciate any help on this.

Cheers,

Tanya

 

[Dossfm0q]

.TanyaD

Columns(Cnt).Hidden = Truet to Columns(Cnt).EntireColumn.Hidden = Truet

I sorry Fluff

Sub Filter(StrtDate As Date, EndDate As Date, Items As String)
Dim I As Long
Dim WS As Worksheet
Dim LC As Long
Dim MyDate As String
Set WS = ThisWorkbook.Worksheets("STAFF")


With WS
    .Cells.EntireColumn.Hidden = False
    LC = .Cells(2, .Columns.Count).End(xlToLeft).Column
    Itm = Split(Items, ",")
    .Cells(2, 2).Resize(1, LC - 1).EntireColumn.Hidden = True
    For I = 1 To LC
    DT = Trim(.Cells(1, I).Value)
    HS = .Cells(2, I).Value
    If Trim(DT) <> "" Then MyDate = DT
        For h = LBound(Itm) To UBound(Itm)
            If MyDate <> "" Then
                If InStr(1, HS, Itm(h)) > 0 And CDate(MyDate) >= CDate(StrtDate) And CDate(MyDate) <= CDate(EndDate) Then
                .Columns(I).EntireColumn.Hidden = False
                Else
                End If
            End If
        Next
    Next
End With
End Sub

 

[Fluff]

Sadly that's not working, it's hiding all the columns?

What row are your dates in?
Also, Do you have a date in every column?

 

[TanyaD]

Hi,

Dates are in row 2, starting in B2. There is a date in every column (so the same date repeats, e.g., date 1 is in each of B2 - G2, as goes across Staff1 - Staff 6). Staff are in row 3, starting in C2.

Thanks

 

[Fluff]

Run this & let me know what the message box says

Sub Chk()
MsgBox IsDate(Range("B2"))
End Sub

 

[TanyaD]

The message back says 'true'

 

[Fluff]

What is your regional date settings & how are you entering the dates into the input box?

 

[TanyaD]

Sorry for my delay in replying! My regional date settings are English (New Zealand), the format is set as dd/mm/yyyy, although you can also enter it as d/m/yy and it will automatically change it to dd/mm/yyyy. Hope that's of some help

 

[Fluff]

I've uploaded a test file to here https://1drv.ms/x/s!AtYrlcV2NWPFgQxXgvBb35kVwtnu
does that work for you?

 

[TanyaD]

That worked brilliantly, thank you so much for all your help, it's running like a charm

 

[Fluff]

Glad we could help & thanks for the feedback