vba to filter dates

[don matteo]

Hey guys
I have 100s of rows in column "I" with the date format

2017-04-24 14:10:23

<tbody>
</tbody><colgroup><col></colgroup>

I want to create a macro to filter and hide anything that is 48 hours old, and only show me the items older then 48 hours.

I have tried to record a macro by my filtering it but I cant get that to work because theres 100s of rows and the dates are never the same due to the time stamp

any help will be appreciated

 

[don matteo]

this is what im working with but it seems to just hide everything

Private Sub hide()
Dim cell As Range
For Each cell In Range("i2:i2000")
If cell.Value <= Date Or cell.Value >= Now - 2 Then
cell.EntireRow.Hidden = True
End If
Next
End Sub

 

[MrIfOnly]

Have you considered using autofilter? This code should do what you are asking for:

Public Sub filterdates()
Dim comparedate As Date
    Worksheets(1).AutoFilterMode = False
    comparedate = Now - 2
    Worksheets(1).Range("A:I").AutoFilter Field:=9, Criteria1:="<" & Format(comparedate, "yyyy/mm/dd h:mm:ss")
End Sub

Regards,

CJ

 

[don matteo]

it still just hides everything

 

[SpillerBD]

it still just hides everything

Filtered and Hidden are two different things.
This can be demonstrated using regular Copy and Paste. Copy and Paste a range with Hidden Rows (or Columns) you will still paste the hidden rows. Filter method no issue.
You also run into the same issue with Calculating using SUBTOTAL. Hidden cells are still in the calculation when (at least when I use Subtotal) I only want the filtered results Subtotaled.

 

[MrIfOnly]

Can you post a sample of your data please.

 

[don matteo]

this is how it in column "I" but there's approx. 1300 rows 2017-04-24 14:10:23

2017-04-24 15:20:43

2017-04-25 21:13:22

2017-04-26 19:11:37

2017-04-26 23:35:55

2017-04-26 23:41:13

2017-04-27 14:16:13

2017-04-27 18:25:44

2017-04-27 18:40:19

2017-04-27 23:54:47

2017-04-28 19:11:22

2017-05-17 19:21:59

2017-05-22 15:13:41

2017-05-22 17:09:15

<colgroup><col width="157" style="width: 118pt; mso-width-source: userset; mso-width-alt: 5741;"> <tbody> </tbody>

 

[MrIfOnly]

I'm still missing the big picture on what your worksheet looks like, but look at the image below. This is the filtered result I got when using the above code on some sample data. If you hover over the filter icon in cell I1, you should see the filter pop-up which shows, correctly, that it is filtering for everything before 48 hours ago.

 

[SpillerBD]

Have you considered using autofilter? This code should do what you are asking for:

Public Sub filterdates()
Dim comparedate As Date
    Worksheets(1).AutoFilterMode = False
    comparedate = Now - 2
    Worksheets(1).Range("A:I").AutoFilter Field:=9, Criteria1:="<" & Format(comparedate, "yyyy/mm/dd h:mm:ss")
End Sub

Regards,

CJ

I don't think FORMAT is right. You just need the compare value.

Public Sub filterdates()
Dim comparedate As Date
    Worksheets(1).AutoFilterMode = False
    comparedate = Now - 2
    [B]Worksheets(1).Range("A:I").AutoFilter Field:=9, Criteria1:="<" & comparedate[/B]
End Sub

 

[MrIfOnly]

@SpillerBD: Looks like you are correct: It works with or without the Format part. Thanks for pointing this out.

Also, I realize that I posted the screenshot above with the column of dates not formatted as the OP stated, but tested this with both the shown format and the yyyy/mm/dd hh:mm:s format.

CJ