Hide rows if date less than today

**jpruffle** · 05-15-2009, 02:44 PM

Hi,

I wish to be able to hide rows if the date value in column B is less than "TODAY" i.e. hide old data.

I have tried the following code but it doesn't work:

Sub HideRowsDate()

Dim cell As Range
For Each cell In Range("B10:B1000")
If cell.Value <= Today() Then
cell.EntireRow.Hidden = True
End If
Next
End Sub

It doesn't recognise TODAY(). I would also like it to ignore blank or error entries if possible.

Thank you in advance

**Special-K** · 05-15-2009, 02:58 PM

Replacing TODAY() with Now (no brackets) worked for me

**jpruffle** · 05-15-2009, 03:18 PM

Thanks for the reply.

The Now works great but I also need it to hide blank entries, as I have to pre set the range and there is 1000 rows! Is this possible?

Thanks

**shg** · 05-15-2009, 03:18 PM

The VBA equivalent to TODAY() is Date.

**jpruffle** · 05-16-2009, 11:58 AM

Thank you for the reply.

But again is it possible for the routine to ignore cells with blank data? I only have about 30 dates in there at the moment but the range is upto 1000. As such when I run the routine over 900 rows are being hidden.

Thanks in advance.

**shg** · 05-16-2009, 12:02 PM

Sure:

Sub HideRowsDate()
    Dim cell    As Range
    
    For Each cell In Range("B10:B1000")
        If cell.Value < Date And cell.Value <> Empty Then cell.EntireRow.Hidden = True
    Next
End Sub

**jpruffle** · 05-16-2009, 12:09 PM

Thanks shg,

That works great!

**orsm6** · 08-28-2017, 12:55 AM

I tried this code but for me it still shows all rows in the range containing dates less than today's date. I want to only show rows with today's date, how would I need to modify the code?

Sub HideRowsDate()
    Dim cell    As Range
    
    For Each cell In Range("c1:c1000")
        If cell.Value < Date And cell.Value <> Empty Then cell.EntireRow.Hidden = True
    Next
End Sub

TIA

**FDibbins** · 08-28-2017, 02:22 AM

orsm6 welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.