VBA/Excel - Sort by date range

Question

I'm trying to sort an Excel worksheet by date range using VBA. I need to filter for all entries that are within a week of the current date.

The worksheet is arranged into columns (A to S). Column 'I' stores the dates I want filtered.

Below is the latest code i've tried (i've tried a few variations... and just cant figure it out).

Dim A As Date
Dim b As String
Dim c As Long
A = Date
A = DateSerial(Year(A), Month(A), Day(A))
b = Format(A - 7, "yyyy, mm, dd")
c = b    
With ActiveSheet
    .Range("$A:$S").AutoFilter Field:=9, Criteria:=">" & c
    End With

Thanks in advance for your help with this.

GTD

Is this format "yyyy, mm, dd" the format values in column I are formatted? that is very surprising way of storing a Date — KKowalczyk
– KKowalczyk, Commented May 21, 2015 at 10:29

KKowalczyk · Accepted Answer · 2015-05-21 11:11:57Z

1

try this:

 ActiveSheet.Range("$A$1:$S$" & ActiveSheet.UsedRange.Rows.Count).AutoFilter Field:=9, Criteria1:=">" & Format(Date - 7, "mm/dd/yyyy")

answered May 21, 2015 at 11:11

KKowalczyk

4333 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kᴀτᴢ · Accepted Answer · 2015-05-21 11:08:06Z

0

Try this:

Dim A As Date
A = Date

With ActiveSheet
    .Range("$A:$S").AutoFilter Field:=3, Criteria1:=">" & A - 7
End With

answered May 21, 2015 at 11:08

Kᴀτᴢ

2,1966 gold badges33 silver badges60 bronze badges

1 Comment

GtDriver Over a year ago

Thanks for looking into this for me. It didn't work as i needed it to i'm afraid. It did filter though :). For me, 'Field:=3' should have been 'Field:=9'. Also, it seemed to remove all dates from the filter.

Collectives™ on Stack Overflow

VBA/Excel - Sort by date range

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related