vba help - autofilter date column challenge

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
976
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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?
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
Hi Yongle,

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




Thanks
mg
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top