Filter all rows where date is between today and 30 days from today?

[Human_doing]

Hi all,

Can someone please help with how to filter a worksheet so only rows where the date field shows a date between today's date and 30 days from today shows up? If anyone can explain the general principle then I can follow that whilst recording a macro and put it in to the overall VBA, I can't see how 'filter' or 'autofilter' on Excel 2003 can do this and haven't found the right VBA on this forum to produce the right result,

Any help much appreciated

 

[Andrew Poulsom]

You can use a Custom AutoFilter. VBA example for column A on Sheet1:

Sub Test()
    Dim Sh As Worksheet
    Set Sh = Worksheets("Sheet1")
    With Sh.Range("A1").CurrentRegion
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:=">=" & CLng(Date), Operator:=xlAnd, Criteria2:="<=" & CLng(Date + 30)
    End With
End Sub

 

[Human_doing]

Thanks for your quick reply.

Could you please tell me what the exact code should be if the date field is column C, headed 'Review Date'?

Thanks

 

[Andrew Poulsom]

Try changing Field:=1 to Field:=3.

 

[Human_doing]

Thanks I should have spotted that haha, works perfectly much appreciated,

Thanks

 

[za460c]

I have a very similar one, but I just cannot use the same...
I have several file sources (all around 2-300.000 lines) the same format each, in column Z the date what I need. My pivot with the macro is in a separate file. In my pivot I want to update the source without removing the other filters and filter always on the last 3 months only. And also on one sheet I have several pivot as well.
Just cannot make it...
Any idea would be appreciated!