VBA Hiding Rows based on Current Date

%3CLINGO-SUB%20id%3D%22lingo-sub-1441885%22%20slang%3D%22en-US%22%3EVBA%20Hiding%20Rows%20based%20on%20Current%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1441885%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I'm%20trying%20to%20write%20a%20vba%20code%20to%20hide%20certain%20rows%20depending%20on%20the%20current%20date%20(i.e.%20if%20it's%20currently%20FY2020%2C%20I%20want%20to%20hide%20the%20FY2021-2023%20rows.%20I've%20pasted%20my%20attempt%20below%20but%20it's%20not%20working%20and%20I%20can't%20quite%20figure%20out%20what%20I'm%20doing%20wrong.%26nbsp%3B%3C%2FP%3E%3CP%3EAppreciate%20any%20help%2C%20thanks%20heaps%20in%20advance%20!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EDim%20FY20S%2C%20FY20E%2C%20FY21S%2C%20FY21E%2C%20FY22S%2C%20FYE22%20As%20Date%0ADim%20Current_Date%20As%20String%0ACurrent_Date%20%3D%20Date%0AFY20S%20%3D%20%237%2F1%2F2020%23%0AFY20E%20%3D%20%236%2F30%2F2021%23%0AFY21S%20%3D%20%237%2F1%2F2021%23%0AFY21E%20%3D%20%236%2F30%2F2022%23%0AFY22S%20%3D%20%237%2F1%2F2022%23%0AFY22E%20%3D%20%236%2F30%2F2023%23%0A%0ASelect%20Case%20Current_Date%0A%0ACase%20FY20S%20To%20FY20E%0ARange(%22FinancialYear21%22).Rows.Hidden%20%3D%20True%0ARange(%22FinancialYear22%22).Rows.Hidden%20%3D%20True%0ARange(%22FinancialYear23%22).Rows.Hidden%20%3D%20True%0A%0ACase%20FY21S%20To%20FY21E%0ARange(%22FinancialYear21%22).Rows.Hidden%20%3D%20False%0ARange(%22FinancialYear22%22).Rows.Hidden%20%3D%20True%0ARange(%22FinancialYear23%22).Rows.Hidden%20%3D%20True%0A%0ACase%20FY22S%20To%20FY22E%0ARange(%22FinancialYear21%22).Rows.Hidden%20%3D%20False%0ARange(%22FinancialYear22%22).Rows.Hidden%20%3D%20False%0ARange(%22FinancialYear23%22).Rows.Hidden%20%3D%20True%0A%0AEnd%20Select%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1441885%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1441924%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Hiding%20Rows%20based%20on%20Current%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1441924%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F689705%22%20target%3D%22_blank%22%3E%40Ky_Heff%3C%2FA%3E%26nbsp%3B%20I'm%20sure%20this%20isn't%20the%20problem%20but%20you%20realize%20that%20today's%20date%20is%202020-06-04%20and%20hence%20doesn't%20meet%20any%20of%20those%20criteria%20(Your%20first%20range%20starts%20next%20month).%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi I'm trying to write a vba code to hide certain rows depending on the current date (i.e. if it's currently FY2020, I want to hide the FY2021-2023 rows. I've pasted my attempt below but it's not working and I can't quite figure out what I'm doing wrong. 

Appreciate any help, thanks heaps in advance ! 

 

 

Dim FY20S, FY20E, FY21S, FY21E, FY22S, FYE22 As Date
Dim Current_Date As String
Current_Date = Date
FY20S = #7/1/2020#
FY20E = #6/30/2021#
FY21S = #7/1/2021#
FY21E = #6/30/2022#
FY22S = #7/1/2022#
FY22E = #6/30/2023#

Select Case Current_Date

Case FY20S To FY20E
Range("FinancialYear21").Rows.Hidden = True
Range("FinancialYear22").Rows.Hidden = True
Range("FinancialYear23").Rows.Hidden = True

Case FY21S To FY21E
Range("FinancialYear21").Rows.Hidden = False
Range("FinancialYear22").Rows.Hidden = True
Range("FinancialYear23").Rows.Hidden = True

Case FY22S To FY22E
Range("FinancialYear21").Rows.Hidden = False
Range("FinancialYear22").Rows.Hidden = False
Range("FinancialYear23").Rows.Hidden = True

End Select

 

 

1 Reply
Highlighted

@Ky_Heff  I'm sure this isn't the problem but you realize that today's date is 2020-06-04 and hence doesn't meet any of those criteria (Your first range starts next month).