Auto Hide Columns and/or Rows using VBA based on cell value (validation list)

I haven't changed the Merged cells, I presume you are referring to the Week Numbers. Merged Cells can screw up many things

Quote from Tom.Jones

Hi,

@ darth_chunk
This one, SuperHero Patrol_VBA_TEST V2.xlsm(26.6 KB, 1 view), from Carim, is working very well

I tested SuperHero Patrol_VBA_TEST V3.xlsm(25.7 KB, 1 view)and it's not working.
If you choose any month, in A1, all columns till 'NI' column, are hidden. Have you tested?

royUK,

Yes, I downloaded (long time ago) that files, from links, and I used them. Thank you. :thumbcoo:

Display More

My version 3 works fine for me, I wouldn't have attached without testing!

My code only hides and displays rows & columns, it doesn't change Merged Cells.. I formatted the grid of "day" cells as a Table and cells within a Table cannot be merged. I was planning to use other Table features.

This is similar to a staff tracker that I created for work, but I didn't have the daily view in mine. I'd been meaning to add this and your post prompted me to have a go. I thought of adding a dashboard with a summary for staff members, in which a Table would help create the formulas.

Hello Roy,

Like your built-in calendar feature ...

Just out of curiosity ... Could selecting a month in cell E1 ... be reflected in your Column D ... Start and End Date ...?

Cheers

I could but the month selection works independently of those date cells

Change the Worksheet Change code to this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rCl As Range
    Dim y As Integer, m As Integer


    With Target
        If .CountLarge > 1 Then Exit Sub
        If Intersect(Target, Range("D1:E2")) Is Nothing Then Exit Sub


        lRw = Range("E1").CurrentRegion.Rows.Count
        lCol = Range("E1").CurrentRegion.Columns.Count


        Application.ScreenUpdating = False


        Select Case .Address
        Case "$E$1"
            If .Value = "Select Date" Then
                Range(Cells(1, 6), Cells(1, lCol)).EntireColumn.Hidden = False
                Exit Sub
            Else


                Range("D1") = DateValue(1 & "/" & Application.WorksheetFunction.Match(Range("E1"), Sheets("Lookups").Range("B4:B15"), 0) & "/" & Year(Range("F1")))
                Range("D2") = Application.WorksheetFunction.EoMonth(Range("D1"), 0)
                DisplayRange
            End If


        Case "$E$2"
            If .Value = "Select SuperHero" Then
                Range(Cells(5, 5), Cells(lRw, 5)).EntireRow.Hidden = False
                Exit Sub
            End If


            For Each rCl In Range(Cells(5, 5), Cells(lRw, 5)).Cells
                rCl.EntireRow.Hidden = Not rCl.Value = .Value
            Next rCl


        Case "$D$1", "$D$2"
            DisplayRange
        End Select


    End With
    Application.ScreenUpdating = True
End Sub

You can now delete the button because the code will run automatically when the month is selected or a range of dates entered