Home

Hide Columns dependant on Cell values

%3CLINGO-SUB%20id%3D%22lingo-sub-550498%22%20slang%3D%22en-US%22%3EHide%20Columns%20dependant%20on%20Cell%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-550498%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20number%20of%20worksheets%20where%20I%20would%20like%20to%20hide%20rows%20dependant%20on%20the%20following%20criteria.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20if%20the%20date%20in%20the%20past.%20Meaning%20if%20the%20date%20is%20beyond%207%20days%20ago%20compared%20to%20the%20current%20date%20For%20example%20cell%20B2.%3C%2FP%3E%3CP%3E2%20And%20if%20the%20value%20in%20a%20cell%20offset%20by%2010%20in%20the%20same%20column%20is%20%26lt%3B%26gt%3B%200%20(example%20B11)%20then%20hide%20the%20entire%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20VBA%20button%20on%20the%20work%20sheet%20triggering%20the%20following%20VBA%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20ShowCurrentWeekOnly()%3CBR%20%2F%3EDim%20c%20As%20Range%3C%2FP%3E%3CP%3EApplication.EnableEvents%20%3D%20False%3C%2FP%3E%3CP%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3EFor%20Each%20c%20In%20Range(%22B2%3AQ2%22)%3CBR%20%2F%3EIf%20((Evaluate(%22%3Dweeknum(today())%22)%20%26lt%3B%26gt%3B%20(Evaluate(%22weeknum(c.Value)%22)))%20And%20(c.Offset(9%2C%200).Value%20%3D%200))%20Then%3CBR%20%2F%3Ec.EntireColumn.Hidden%20%3D%20True%3CBR%20%2F%3EElseIf%20(Evaluate(%22%3Dweeknum(today())%22)%20%3D%20(Evaluate(%22weeknum(c.Value)%22)))%20Then%3CBR%20%2F%3Ec.EntireColumn.Hidden%20%3D%20False%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%3CBR%20%2F%3EOn%20Error%20GoTo%200%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20hides%20all%20the%20columns%20even%20the%20columns%20in%20the%20future.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20a%20newby%20to%20VBA%20so%20some%20help%20would%20be%20grateful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVonryan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-550498%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-550831%22%20slang%3D%22en-US%22%3ERe%3A%20Hide%20Columns%20dependant%20on%20Cell%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-550831%22%20slang%3D%22en-US%22%3EYou%20want%20to%20hide%20the%20row%20if%20the%20value%2010%20rows%20down%20is%20%26lt%3B%26gt%3B0%2C%20but%20your%20statement%20says%3CBR%20%2F%3E(c.Offset(9%2C%200).Value%20%3D%200))%3CBR%20%2F%3EDon't%20you%20want%20it%20to%20say%3F%3CBR%20%2F%3E(c.Offset(9%2C%200).Value%20%26lt%3B%26gt%3B%200))%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-556403%22%20slang%3D%22en-US%22%3ERe%3A%20Hide%20Columns%20dependant%20on%20Cell%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-556403%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20George%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20correct%20and%20I%20changed%20this%20in%20my%20continued%20trials%20to%20get%20this%20working.%20After%20internet%20research%20i%20have%20modified%20my%20code%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20ShowCurrentWeekOnly()%3CBR%20%2F%3EDim%20c%20As%20Range%2C%20msgValue%20As%20Long%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3C%2FP%3E%3CP%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3EFor%20Each%20c%20In%20Range(%22B2%3AQ2%22)%3CBR%20%2F%3EmsgValue%20%3D%20%5Bweeknum(c.Value)%5D%3CBR%20%2F%3EMsgBox%20%22Returned%20value%20is%20%22%20%26amp%3B%20msgValue%3CBR%20%2F%3EIf%20%5Bweeknum(c.Value)%5D%20%26lt%3B%26gt%3B%20%5Bweeknum(date())%5D%20Then%3CBR%20%2F%3E%3CBR%20%2F%3Ec.EntireColumn.Hidden%20%3D%20True%3CBR%20%2F%3EElseIf%20%5Bweeknum(c.Value)%5D%20%3D%20%5BWeeknum(Date)%5D%20Then%3CBR%20%2F%3Ec.EntireColumn.Hidden%20%3D%20False%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%3CBR%20%2F%3EOn%20Error%20GoTo%200%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20use%20the%20message%20box%20to%20return%20the%20values%20i%20am%20trying%20to%20analyse.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20use%20msgValue%20%3D%20c.Value%20the%20message%20box%20returns%20the%20date%20in%20excel%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20use%20msgValue%20%3D%20%5Bweeknum(c.Value)%5D%26nbsp%3B%3CSPAN%3E%26nbsp%3Bthe%20message%20box%20returns%200.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20know%20the%20issue%20is%20here%20somewhere%20but%20I%20cannot%20get%20the%20function%20%5Bweeknum(c.Value)%5D%20to%20return%20a%20week%20number.%20if%20I%20use%20the%20formula%20%3Dweeknum(B2)%20direct%20in%20the%20worksheet%20it%20return%20the%20week%20number%20associated%20with%20the%20cell%20date.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20internet%20led%20me%20to%20believe%20that%26nbsp%3B%5Bweeknum(c.Value)%5D%20is%20the%20same%20as%20evaluate(%22%3Dweeknum(c.Value)%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EYour%20help%20would%20be%20gratefully%20appreciated.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EVonryan%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
vonryan
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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies