Problems with reading dates in Autofilter in Excel using VBA

Hi, I am trying to filter between dates using a Macro and auto-filter in Excel. Yet, its reading the dates in American format, eg
, instead of filtering between 07/09/2015 and 14/09/2015, its filtering between 09/07/2015 and 09/14/2015. Can anyone fix it?

I am using this code:

Dim strdate, strdate1 As String

strdate = Sheets("Macros").Range("Startdate")


strdate1 = Sheets("Macros").Range("Finishdate")

    Sheets("All trades").Select
    Rows("1:1").Select
    Selection.AutoFilter
   ' strdate = Application.InputBox("please delete all trades between which dates?")

    strdate = Format(strdate, "dd/mm/yyyy")
   
    ActiveSheet.Range("$A$1:$AF$111584").AutoFilter Field:=6, Criteria1:= _
        ">=" & strdate, Operator:=xlAnd
       
        strdate1 = Format(strdate1, "dd/mm/yyyy")
        '  strdate1 = Application.InputBox("please delete all trades between which dates?")
    ActiveSheet.Range("$A$1:$AF$111584").AutoFilter Field:=7, Criteria1:= _
        "<=" & strdate1, Operator:=xlAnd
'
    Cells.Select
Commentary-Analysis.xlsm
JustinFinancial ControlAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try this...
Sub AutofilterAndDelete()
Dim ws As Worksheet
Dim lr As Long
Dim strdate As Double

Application.ScreenUpdating = False
Set ws = Sheets("All trades")
lr = ws.Cells(Rows.Count, "F").End(xlUp).Row

strdate = Sheets("Macros").Range("Startdate").Value

Sheets("All trades").AutoFilterMode = 0

With Sheets("All trades").Rows(1)
     .AutoFilter field:=6, Criteria1:=">=" & strdate
     If ws.Range("F1:F" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
        ws.Range("F2:F" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
     End If
     .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

Open in new window

JustinFinancial ControlAuthor Commented:
It looks good, but I have a auto-filter condition on the 7th column too so that the start date is after the 7th of Sep and the finish date is before the 14th of Sep. Will this work?

Sub AutofilterAndDelete()
Dim ws As Worksheet
Dim lr As Long
Dim strdate, strdate1 As Double

'Application.ScreenUpdating = False
Set ws = Sheets("All trades")
lr = ws.Cells(Rows.Count, "F").End(xlUp).Row

strdate = Sheets("Macros").Range("Startdate").Value
strdate1 = Sheets("Macros").Range("Finishdate").Value


Sheets("All trades").AutoFilterMode = 0

With Sheets("All trades").Rows(1)
     .AutoFilter field:=6, Criteria1:=">=" & strdate
   .AutoFilter field:=7, Criteria1:="<=" & strdate1
     If ws.Range("F1:F" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
        ws.Range("F2:F" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
     End If
     .AutoFilter
End With
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Yes it should work. Did you try that?

Also while declaring variables always declare datatype with each variable. When you declares like this
Dim strdate, strdate1 As Double

Open in new window

That way strdate is declared as Variant and strdate1 as Double. So it should be correctly declared as
Dim strdate As Double, strdate1 As Double

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.