VBA Hiding Rows based on Current Date

Copper Contributor

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

@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).