Excel date vba filter

[Tommy5]

Hi guys,

Been stuck on this for days now so I'm resorting to asking for assistance on this one.

I'm trying to filter between two dates in dd/mm/yyyy format ( my PC is formatted this way too in regional settings), but after I run the code, the date "custom filter" has mixed up the days and months.

Here is my code, where c3 is the start date, formatted dd/mm/yyyy, and c4 is end date, formatted dd/mm/yyyy, Sheet10.Range("a4") is the table of data, and column 34 is the column I need to apply the date filter to.

Dim DateBegin As Date
Dim DateEnd As Date
Dim Rng As Range

Set Rng = Sheet10.Range("a4")
DateBegin = Format(Sheet1.Range("c3").Value, "dd/mm/yyyy")
DateEnd = Format(Sheet1.Range("c4").Value, "dd/mm/yyyy")
With Rng
.AutoFilter Field:=34, Criteria1:=">" & DateBegin, _
Operator:=xlAnd, Criteria2:="<=" & DateEnd

What am I doing wrong here? Would be so grateful if somebody could help me out on this one,

 

[dmt32]

Hi,
welcome to forum

dates in AutoFilter you need to think US date format – one method used to overcome this is to coerce your date to long data type using type conversion function Clng.

Dim DateBegin As Long
Dim DateEnd As Long
Dim Rng As Range

Set Rng = Sheet10.Range("a4")
DateBegin = CLng(Sheet1.Range("c3").Value)
DateEnd = CLng(Sheet1.Range("c4").Value)

Rng.AutoFilter Field:=34, Criteria1:=">" & DateBegin, _
                        Operator:=xlAnd, _
                        Criteria2:="<=" & DateEnd

Note: variables have been changed from Date to Long data type.

If still have issues, then perhaps using the DateSerial function to pass to Long variables will resolve

Dave

 

[Fluff]

Hi & welcome to MrExcel.
How about

With Sheet10
   .Range("A4").AutoFilter 16, ">" & Sheet1.Range("C3").Value2, _
      xlAnd, "<=" & Sheet1.Range("C4").Value2
End With

 

[Tommy5]

Hi,
welcome to forum

dates in AutoFilter you need to think US date format – one method used to overcome this is to coerce your date to long data type using type conversion function Clng.

Dim DateBegin As Long
Dim DateEnd As Long
Dim Rng As Range

Set Rng = Sheet10.Range("a4")
DateBegin = CLng(Sheet1.Range("c3").Value)
DateEnd = CLng(Sheet1.Range("c4").Value)

Rng.AutoFilter Field:=34, Criteria1:=">" & DateBegin, _
                        Operator:=xlAnd, _
                        Criteria2:="<=" & DateEnd

Note: variables have been changed from Date to Long data type.

If still have issues, then perhaps using the DateSerial function to pass to Long variables will resolve

Dave

Thank you so much, that worked!

Could you provide an example for using the DateSerial function to pass to Long variables

 

[dmt32]

Thank you so much, that worked!

Could you provide an example for using the DateSerial function to pass to Long variables

With Sheet1.Range("c3")
    DateBegin = DateSerial(Year(.Value), Month(.Value), Day(.Value))
End With

With Sheet1.Range("c4")
    DateEnd = DateSerial(Year(.Value), Month(.Value), Day(.Value))
End With

In most cases, clng coercion normally works with dates for Autofilter

many thanks for feedback, glad we were able to assist you

Dave