Creating a Macro (Ctrl+) Button for Hiding/Unhiding Rows based on Specified Date

[AnissaCosby]

Good Afternoon!

In the Excel Spreadsheet that I'm using for work, we keep a list of reservations for students going out on clinicals. We put in dates they are reserved to go out to the site and we get tons and tons of reservations. I wanted to know how I could make a quick recorded Macro so that when someone presses for example Ctrl+Alt+H, it hides the reservations that are older than 60 days. And then if they press Ctrl+Alt+U, it unhides the rows. This would be based on the ending date of their reservation which starts at Column E2 and will go on and on to E2000 (or more). It's pretty much making a quick filter I guess.

Thanks a ton for your help!
-Anissa

 

[XLfriend777]

Hi AnissaCosby - Welcome to the forum. You will find all kinds of great info and help here! I think you might have suggested a solution yourself. You can turn on filters and then select Date Filters -> After (then put in a date 60 days ago). The column will then only show rows that have a date after the date you selected. Hope this helps.

 

[ParamRay]

An autofilter would be best, but if you must have a VBA solution using shortcut keys, try the following. (Run the first Sub only.)

Private Sub SetKeys()

  Application.OnKey "^%{h}", "HideRows"
  Application.OnKey "^%{u}", "UnhideRows"

End Sub

'--------------------------------------------------------------------

Private Sub HideRows()
  
  Dim DateCol As Range
  Dim Cell As Range
  
  Set DateCol = Intersect(Columns("E"), ActiveSheet.UsedRange)
  UnhideRows
  
  For Each Cell In DateCol
    If Cell.Row > 1 Then
      If IsDate(Cell.Value) Then
        If Cell.Value < Date - 60 Then
          Cell.EntireRow.Hidden = True
        End If
      End If
    End If
  Next Cell

End Sub

'--------------------------------------------------------------------

Private Sub UnhideRows()

  Rows.Hidden = False

End Sub

 

[AnissaCosby]

Hi there!

A VBA solution would be best because my coworkers that share the spreadsheet are often using the filters for other things. It would be best if they were filtering by a students name, they don't see the past dates to avoid confusion. I tried the VBA code but it isn't working. Do I have to create a module when I'm viewing the sheet's code? I'm only just grasping VBA and coding. I'm not very good at it.

Thanks again!