VBA: Hide rows based on value (date)

[Marbella]

Hi,

I have the below code to hide rows based on the date in column "C". What I am trying to do is hide rows older than today's date. It's working but not all rows are being hidden.

Any suggestion?

Thank you in advance.

Windows 7, MS 2010.

Private Sub Hidebtn_Click()
Dim myRg As Range
Set myRg = Range([C1].End(xlDown), [C80].End(xlUp))


For Each cell In myRg
If cell.Value >= Now Then
cell.EntireRow.Hidden = True
End If
Next cell


Set myRg = Nothing
End Sub

 

[jim may]

In code It is sometimes best to use date versus now; I'd also step thru code and after passing your range assigment (in the immediate window) co the .address to verify three rng is correct.

 

[Marbella]

Thank you Jim for your quick answer. Could you please help me by enhancing the aforementioned code.

Once again, thank you

Omar

In code It is sometimes best to use date versus now; I'd also step thru code and after passing your range assigment (in the immediate window) co the .address to verify three rng is correct.

 

[jim may]

I'd only substitute the text "date" for "now". That alone might do it.

 

[Marbella]

I'd only substitute the text "date" for "now". That alone might do it.

I replaced "now" with "date", but nothing happened at all.

Private Sub Hidebtn_Click()
Dim myRg As Range
Set myRg = Range([C1].End(xlDown), [C80].End(xlUp))


For Each cell In myRg
If cell.Value = Date Then
cell.EntireRow.Hidden = True
End If
Next cell


Set myRg = Nothing
End Sub

 

[Marbella]

I got it working!

The code below to show dates within a week and hide the rest.

Private Sub Hidebtn_Click()
Dim cell As Range
For Each cell In Range("C2:C50")
If cell.Value <= Date Or cell.Value >= Date + 6 Then
cell.EntireRow.Hidden = True
End If
Next
End Sub

To show all hidden rows.

Private Sub Showbtn_Click()
Rows.EntireRow.Hidden = False
End Sub

 

[jim may]

Glad you got it working. Jim