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]

hey guys thanks,
so this is a screen shot of my data

and this is how it looks after I run the macro

 

[don matteo]

i still cant seen to get it to work i attached some screen shots

 

[Peter h]

I can't see the screenshots for some reason. They're just little x's (probably because I'm on my work computer). And MrIfOnly seems to have had a solution that he was able to get to work for him. I just thought I'd share this simple little code that seems to do what you're requesting. You'll have to make some adjustments to make it fit your application.

Dim lRow As Long
Dim rng1 As Range
lRow = Sheet1.Range("I" & Rows.Count).End(xlUp).Row
Set rng1 = Sheet1.Range("I1:I" & lRow)
For Each Cell In rng1
    If CDate(Cell.Value) <= Now - 2 Then
        Sheet1.Range("A" & Cell.Row).EntireRow.Hidden = True
    End If
Next Cell

 

[don matteo]

thank you I will try it when I get to the office!

I can't see the screenshots for some reason. They're just little x's (probably because I'm on my work computer). And MrIfOnly seems to have had a solution that he was able to get to work for him. I just thought I'd share this simple little code that seems to do what you're requesting. You'll have to make some adjustments to make it fit your application.

Dim lRow As Long
Dim rng1 As Range
lRow = Sheet1.Range("I" & Rows.Count).End(xlUp).Row
Set rng1 = Sheet1.Range("I1:I" & lRow)
For Each Cell In rng1
    If CDate(Cell.Value) <= Now - 2 Then
        Sheet1.Range("A" & Cell.Row).EntireRow.Hidden = True
    End If
Next Cell

 

[MrIfOnly]

Hopefully Peter's solution will work for you.

As for mine: I tried various formatting styles and can't seem to duplicate your issue. After running my code, when I hover over the filter icon in column I it shows "Before: " and the date minus 48 hours not the "Less than" and the date code that is shown in your screenshot. What version of Excel are you running?

 

[don matteo]

I am running 2013, what I ended up doing was using a MID formula to remove the timestamp from the date and filtered it out that way, im assuming the format of my data was just giving me some issues

thank you all for all the help! I really needed it!!![

QUOTE=MrIfOnly;4835460]Hopefully Peter's solution will work for you.

As for mine: I tried various formatting styles and can't seem to duplicate your issue. After running my code, when I hover over the filter icon in column I it shows "Before: " and the date minus 48 hours not the "Less than" and the date code that is shown in your screenshot. What version of Excel are you running?[/QUOTE]