Hiding rows based on a date function

[Melvin Purvis]

I have a spread sheet which can vary from 8k to 12k lines long.

Row 1 contains header information.

Column Y can contain either blank cells, or cells with dates.

Excel 2003.

I need to run through all the rows, look at the value in column Y, and
determine if it is more than 100 days from todays date. "today" meaning
whatever the date is when the macro is run. If the date in Y is more than 100
days from todays date, I need to hide the entire row.

Can anybody provide help with this?

Many thanks in advance!

 

[Mike H]

Hi,

You don't say whether it's 100 days in the future or the past so this
assumes the future. Right click your sheet tab, view code and paste this in
and run it.

Sub Marine()
Dim MyRange, MyRange1 As Range
lastrow = Cells(Rows.Count, "Y").End(xlUp).Row
Set MyRange = Sheets("Sheet1").Range("Y2:Y" & lastrow)
For Each c In MyRange
If c.Value > Now + 100 Then 'Change to suit
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.EntireRow.Hidden = True
End If
End Sub

Mike

 

[Mike H]

I left the sheet range in use this instead

Sub Marine()
Dim MyRange, MyRange1 As Range
lastrow = Cells(Rows.Count, "Y").End(xlUp).Row
Set MyRange = Sheets("Sheet1").Range("Y2:Y" & lastrow)
For Each c In MyRange
If c.Value > Now + 100 Then 'Change to suit
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.EntireRow.Hidden = True
End If
End Sub

Mike

 

[Breakfast Guy]

Try this:

Sub hide_rows()
Dim i As Long
For i = Range("Y" & Rows.Count).End(xlUp).Row To 2 Step -1
If Range("Y" & i).Value <> "" And Range("Y" & i) <= Date - 100 Then
Range("Y" & i).EntireRow.Hidden = True
End If
Next i
End Sub


--
Breakfast Guy

Regards,
Breakfast Guy
Forum Moderator
www.thecodecage.com

 

[Mike Fogleman]

Just a note on whether the 100 days is in the past or in the future. If in
the past, both suggestions will work fine. But for in the future, all the
rows would need to be unhidden first, so that those rows that have been
previously hidden as over 100 days, can be retested.

Mike F