vba help - autofilter date column challenge

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
831
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

I want to filter date Column. faced lot of challenges in filtering date.
Finally got below code which works.

Can you share Any other ways of filtering date Column.
Below is the one solution working for me .


VBA Code:
Sub DateFilter()

Dim startDate As Long
Dim endDate As Long

startDate = CLng(DateValue("01/01/2018"))  'Criteria to filter start date
endDate = CLng(DateValue("31/12/2019")) 'Criteria to Filter endDate

With Range("a1").CurrentRegion
    .AutoFilter Field:=1, Criteria1:=">=" & startDate, Operator:=xlAnd, Criteria2:="<=" & endDate

End With

End Sub


Below is my Input Column... where needs to apply filter.

Book6
A
1Invoice Date
201/01/2018
301/01/2018
431/12/2018
531/12/2018
631/12/2018
705/08/2019
805/08/2019
927/09/2019
1014/12/2019
1111/01/2020
1211/01/2020
1311/01/2020
1411/01/2020
1511/01/2020
1611/01/2020
Sheet1




Thanks
mg
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,872
Office Version
  1. 365
Platform
  1. Windows
Dates are tricky

You have a logical method which works
- do you understand why that method works?
- what is the problem that makes you want a different method?
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
831
Office Version
  1. 2010
Platform
  1. Windows
Hi Yongle,


Q - do you understand why that method works?
Ans :=> No , I didn't undestand.

Q - what is the problem that makes you want a different method?
Ans - I saw solution on this forum, Some has used CDATE, Clng without Datevalue. with this approach also autofilter worked.

but in my situation Cdate was not working,
clng without datevalue also not worked. ............ don't know on which situation which approach to use.




Dim startDate As Date
Dim EndDate As Date

'DD/MM/YYYY
startDate = #1/1/2018# ' I was trying to enter 01/01/2018 it gets converted some other dates in macro
EndDate = #12/31/2019# I was trying to enter 31/12/2019 it gets converted some other dates. in macro



Thanks
mg
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,872
Office Version
  1. 365
Platform
  1. Windows
Explanation
- Excel cells contain NUMBERS or STRINGS
- Dates in Excel are (usually) INTEGERS formatted to display like dates

Dim startDate As Long
startDate = CLng(DateValue("01/01/2018"))


DateValue converts a string value to a date value
That value could be converted to an integer using CInt but VBA Integer variable cannot handle values above 32,767

Today is 5 November 2020 and- Excel interprets 44,140 as the "value" of 5 November 2021
Tomorrow is 44,141 etc
32,767 was 16 September 1989

So date values are usually greater than Integer variable can handle
So CLng is used which converts to type Long - which can handle much bigger integers


startDate & endDate in your code are both type Long (= integers)
- which match the underlying value of the cell
- that is why it works

Does that help?
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
831
Office Version
  1. 2010
Platform
  1. Windows
Hi Yongle,

Understood the concept. Thanks for your help . 🕺 (y)




Thanks
mg
 

Watch MrExcel Video

Forum statistics

Threads
1,118,775
Messages
5,574,159
Members
412,574
Latest member
shadowfighter666
Top