Forum Discussion

Brass Contributor

May 08, 2019

Hide Columns dependant on Cell values

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 t...

Brass Contributor

May 08, 2019

You want to hide the row if the value 10 rows down is <>0, but your statement says
(c.Offset(9, 0).Value = 0))
Don't you want it to say?
(c.Offset(9, 0).Value <> 0))

vonryan
Brass Contributor
May 09, 2019
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