Hide row automatically based on date

  • Thread starter Thread starter Munchkin
  • Start date Start date
M

Munchkin

I want my spreadsheet to look the date in colum C of each row. If the date
is more than 4 years old I'd like the row to be hidden. Any way to do this
automatically?
 
Perhaps something like this. I assumed your data in Column C starts in C2
down. HTH Otto

Sub HideRows()
Dim rColC As Range
Dim i As Range
Set rColC = Range("C2", Range("C" & Rows.Count).End(xlUp))
For Each i In rColC
If DateSerial(Year(Date) - 4, Month(Date), Day(Date)) > i.Value Then
i.EntireRow.Hidden = True
End If
Next i
End Sub
 
Try the below macro..You can either run this as a macro or paste the code in
workbook Close event or Open event ...Try and feedback..

Sub HideRows()
Dim lngRow as Long
For lngRow = 1 To Cells(Rows.Count, 3).End(xlUp).Row
If DateDiff("m", Range("c" & lngRow), Date) > 48 And _
0 + Range("c" & lngRow) <> 0 Then Rows(lngRow).Hidden = True
Next
End Sub

If this post helps click Yes
 
Question for Otto

I think the code you supplied here could be really useful to me in a different situation. Could you tell me how the DateSerial line works?

I'm trying to take a big block of data, including column B which contains times in 24h format, and break it up into shifts. 05:00 to 12:59, 13:00 to 21:00, and 21:00 to 04:59. How could I use TimeSerial in a similar way to do this?

Thanks!
 
Back
Top