May 08 2019 05:16 PM
Dear All,
I have a number of worksheets where I would like to hide rows dependant on the following criteria.
1) if the date in the past. Meaning if the date is beyond 7 days ago compared to the current date For example cell B2.
2 And if the value in a cell offset by 10 in the same column is <> 0 (example B11) then hide the entire column.
I have a VBA button on the work sheet triggering the following VBA code.
Sub ShowCurrentWeekOnly()
Dim c As Range
Application.EnableEvents = False
On Error Resume Next
For Each c In Range("B2:Q2")
If ((Evaluate("=weeknum(today())") <> (Evaluate("weeknum(c.Value)"))) And (c.Offset(9, 0).Value = 0)) Then
c.EntireColumn.Hidden = True
ElseIf (Evaluate("=weeknum(today())") = (Evaluate("weeknum(c.Value)"))) Then
c.EntireColumn.Hidden = False
End If
Next
On Error GoTo 0
Application.EnableEvents = True
End Sub
It hides all the columns even the columns in the future.
I am a newby to VBA so some help would be grateful.
Vonryan
May 08 2019 07:07 PM
May 09 2019 08:59 PM
Hi George
You are correct and I changed this in my continued trials to get this working. After internet research i have modified my code as follows:
Sub ShowCurrentWeekOnly()
Dim c As Range, msgValue As Long
Application.EnableEvents = False
On Error Resume Next
For Each c In Range("B2:Q2")
msgValue = [weeknum(c.Value)]
MsgBox "Returned value is " & msgValue
If [weeknum(c.Value)] <> [weeknum(date())] Then
c.EntireColumn.Hidden = True
ElseIf [weeknum(c.Value)] = [Weeknum(Date)] Then
c.EntireColumn.Hidden = False
End If
Next
On Error GoTo 0
Application.EnableEvents = True
End Sub
I use the message box to return the values i am trying to analyse.
When I use msgValue = c.Value the message box returns the date in excel format.
When I use msgValue = [weeknum(c.Value)] the message box returns 0.
I know the issue is here somewhere but I cannot get the function [weeknum(c.Value)] to return a week number. if I use the formula =weeknum(B2) direct in the worksheet it return the week number associated with the cell date.
The internet led me to believe that [weeknum(c.Value)] is the same as evaluate("=weeknum(c.Value)")
Your help would be gratefully appreciated.
Vonryan