View Full Version : Solved: hide columns in other sheet based on dates match
sunilmulay
10-09-2008, 02:40 PM
Hi there
Could someone please help with this? I'm a VBA newbie....
I have two sheets:A and B. Sheet A contains within columns B-Z dates in rows 2 and 3. Dates in row 2 are the start of week dates, and dates in row 3 are the end of week dates. Sheet B has a cell where the user can enter a date. I want to set a button next to it that runs a macro that hides all columns in Sheet A in range B-Z where the date the user entered is not equal to or between the start/end dates of that column.
Any thoughts?
Thanks
S
Hi Sunilmulay,
Are you asking for help w/the code, the button, or both?
Could you post the book or an example thereof?
Thank you so much,
Mark
Bob Phillips
10-09-2008, 03:45 PM
Public Sub Test()
Dim MatchCol As Long
With Worksheets("Sheet A")
.Columns("B:Z").Hidden = False
On Error Resume Next
If MatchCol <> 0 Then
.Columns("B:Z").Hidden = True
.Columns("A").Offset(, MatchCol).Hidden = False
Else
MatchCol = Application.Match(CLng(Worksheets("Sheet B").Range("A1").Value), .Range("B2:Z2"), 1)
If MatchCol <> 0 Then
If Worksheets("Sheet B").Range("A1").Value <= .Range("A2").Offset(1, MatchCol) Then
.Columns("B:Z").Hidden = True
.Columns("A").Offset(, MatchCol).Hidden = False
End If
End If
End If
End With
End Sub
sunilmulay
10-10-2008, 02:05 AM
Hi Guys
thanks for the replies. i tried the above code, but couldn't get it to work - the wrong columns get hidden! Could you please check out the attached simplified file?
I want to hide columns P to V in Stage01-Actuals, where the date entered on Sheet-Internal Dashboard Cell F3 doesn't equal to or lie within the dates in rows 8 and 9 of Sheet Stage01-Actuals
Thanks in advance!!!
Sunil
Bob Phillips
10-10-2008, 03:52 AM
What would happen if the date was say 29th Oct 2008?
sunilmulay
10-10-2008, 06:53 AM
If the user entered 29th of Oct in the second sheet in the attached file above, all columns in the first sheet in range P to V should be hidden except for column S, because 27thOct < 29th Oct < 2nd Nov.
Thanks
S
Were you still looking to solve this Sunimulay? xld's code does work perfectly, you have some 'issues' with the formatting of your "date" cells.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.