Hide Columns dependant on Cell values

Brass Contributor

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

2 Replies
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))

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