I haven't changed the Merged cells, I presume you are referring to the Week Numbers. Merged Cells can screw up many things
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?
Yes, I downloaded (long time ago) that files, from links, and I used them. Thank you. :thumbcoo:
My version 3 works fine for me, I wouldn't have attached without testing!
Not specifically the week numbers. I found in the latest version that the main body of the sheet locks out merging cells and I thought it was something you had imposed intentionally. Either way it's for the best so that the end users don't go around merging everything (as they tend to do) and messing up the code.
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.
I see, that makes much more sense.
Thanks for the clarification. Still works like a charm as far as I'm concerned. Out of curiosity, what other table features were you planning on using?
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.
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 ...?
I could but the month selection works independently of those date cells
Change the Worksheet Change code to this:Code
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