asked on

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

Subodh Tiwari (Neeraj)

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

Justin

ASKER

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

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial