Excel VBA Autofilter not working with Date column












10














I got a problem using AutoFilter with VBA in Excel.



It works well for regular filters, but filtering the date column does not work as intended.
The column is formatted as date, I can filter it manually and absurdly, if I run my code, it filters nothing but when I check the filter and then only click ok (no change being applied to the filter criteria), it starts filtering correctly.



Here is my code:



ws.ListObjects(SheetName).Range.AutoFilter Field:=3, Criteria1 _
:=">" & CDate([datecell]), Operator:=xlAnd, Criteria2:= _
"<=" & CDate(WorksheetFunction.EoMonth([datecell], 3))


Anyone has an idea? It seems to be a common problem, but I have not found a solution.



Thanks in advance.



Edit: Just to add, when I macro record it and run the recorded macro, it does not work either.










share|improve this question
























  • If you change your date cells format to general does it show a number?
    – Sam
    Nov 4 '13 at 12:31










  • Yes, as I said, everything works as it should. There is a problem when the filter is applied. Because the recorded macro does not work either.
    – Spurious
    Nov 4 '13 at 12:40










  • Try converting to text instead. such as CDate([datecell]) change to Format([datecell], "dd-MMM-yy")
    – Sam
    Nov 4 '13 at 12:47












  • Does not work either, see the answer and my comment below. It's really absurd.
    – Spurious
    Nov 4 '13 at 12:54










  • That does seem a bit odd yes.... what value is [datecell] and WorksheetFunction.EoMonth([datecell], 3) if you Debug.Print or msgbox them?
    – Sam
    Nov 4 '13 at 12:56
















10














I got a problem using AutoFilter with VBA in Excel.



It works well for regular filters, but filtering the date column does not work as intended.
The column is formatted as date, I can filter it manually and absurdly, if I run my code, it filters nothing but when I check the filter and then only click ok (no change being applied to the filter criteria), it starts filtering correctly.



Here is my code:



ws.ListObjects(SheetName).Range.AutoFilter Field:=3, Criteria1 _
:=">" & CDate([datecell]), Operator:=xlAnd, Criteria2:= _
"<=" & CDate(WorksheetFunction.EoMonth([datecell], 3))


Anyone has an idea? It seems to be a common problem, but I have not found a solution.



Thanks in advance.



Edit: Just to add, when I macro record it and run the recorded macro, it does not work either.










share|improve this question
























  • If you change your date cells format to general does it show a number?
    – Sam
    Nov 4 '13 at 12:31










  • Yes, as I said, everything works as it should. There is a problem when the filter is applied. Because the recorded macro does not work either.
    – Spurious
    Nov 4 '13 at 12:40










  • Try converting to text instead. such as CDate([datecell]) change to Format([datecell], "dd-MMM-yy")
    – Sam
    Nov 4 '13 at 12:47












  • Does not work either, see the answer and my comment below. It's really absurd.
    – Spurious
    Nov 4 '13 at 12:54










  • That does seem a bit odd yes.... what value is [datecell] and WorksheetFunction.EoMonth([datecell], 3) if you Debug.Print or msgbox them?
    – Sam
    Nov 4 '13 at 12:56














10












10








10


1





I got a problem using AutoFilter with VBA in Excel.



It works well for regular filters, but filtering the date column does not work as intended.
The column is formatted as date, I can filter it manually and absurdly, if I run my code, it filters nothing but when I check the filter and then only click ok (no change being applied to the filter criteria), it starts filtering correctly.



Here is my code:



ws.ListObjects(SheetName).Range.AutoFilter Field:=3, Criteria1 _
:=">" & CDate([datecell]), Operator:=xlAnd, Criteria2:= _
"<=" & CDate(WorksheetFunction.EoMonth([datecell], 3))


Anyone has an idea? It seems to be a common problem, but I have not found a solution.



Thanks in advance.



Edit: Just to add, when I macro record it and run the recorded macro, it does not work either.










share|improve this question















I got a problem using AutoFilter with VBA in Excel.



It works well for regular filters, but filtering the date column does not work as intended.
The column is formatted as date, I can filter it manually and absurdly, if I run my code, it filters nothing but when I check the filter and then only click ok (no change being applied to the filter criteria), it starts filtering correctly.



Here is my code:



ws.ListObjects(SheetName).Range.AutoFilter Field:=3, Criteria1 _
:=">" & CDate([datecell]), Operator:=xlAnd, Criteria2:= _
"<=" & CDate(WorksheetFunction.EoMonth([datecell], 3))


Anyone has an idea? It seems to be a common problem, but I have not found a solution.



Thanks in advance.



Edit: Just to add, when I macro record it and run the recorded macro, it does not work either.







excel vba date autofilter






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 4 '13 at 11:52







Spurious

















asked Nov 4 '13 at 11:44









SpuriousSpurious

79321133




79321133












  • If you change your date cells format to general does it show a number?
    – Sam
    Nov 4 '13 at 12:31










  • Yes, as I said, everything works as it should. There is a problem when the filter is applied. Because the recorded macro does not work either.
    – Spurious
    Nov 4 '13 at 12:40










  • Try converting to text instead. such as CDate([datecell]) change to Format([datecell], "dd-MMM-yy")
    – Sam
    Nov 4 '13 at 12:47












  • Does not work either, see the answer and my comment below. It's really absurd.
    – Spurious
    Nov 4 '13 at 12:54










  • That does seem a bit odd yes.... what value is [datecell] and WorksheetFunction.EoMonth([datecell], 3) if you Debug.Print or msgbox them?
    – Sam
    Nov 4 '13 at 12:56


















  • If you change your date cells format to general does it show a number?
    – Sam
    Nov 4 '13 at 12:31










  • Yes, as I said, everything works as it should. There is a problem when the filter is applied. Because the recorded macro does not work either.
    – Spurious
    Nov 4 '13 at 12:40










  • Try converting to text instead. such as CDate([datecell]) change to Format([datecell], "dd-MMM-yy")
    – Sam
    Nov 4 '13 at 12:47












  • Does not work either, see the answer and my comment below. It's really absurd.
    – Spurious
    Nov 4 '13 at 12:54










  • That does seem a bit odd yes.... what value is [datecell] and WorksheetFunction.EoMonth([datecell], 3) if you Debug.Print or msgbox them?
    – Sam
    Nov 4 '13 at 12:56
















If you change your date cells format to general does it show a number?
– Sam
Nov 4 '13 at 12:31




If you change your date cells format to general does it show a number?
– Sam
Nov 4 '13 at 12:31












Yes, as I said, everything works as it should. There is a problem when the filter is applied. Because the recorded macro does not work either.
– Spurious
Nov 4 '13 at 12:40




Yes, as I said, everything works as it should. There is a problem when the filter is applied. Because the recorded macro does not work either.
– Spurious
Nov 4 '13 at 12:40












Try converting to text instead. such as CDate([datecell]) change to Format([datecell], "dd-MMM-yy")
– Sam
Nov 4 '13 at 12:47






Try converting to text instead. such as CDate([datecell]) change to Format([datecell], "dd-MMM-yy")
– Sam
Nov 4 '13 at 12:47














Does not work either, see the answer and my comment below. It's really absurd.
– Spurious
Nov 4 '13 at 12:54




Does not work either, see the answer and my comment below. It's really absurd.
– Spurious
Nov 4 '13 at 12:54












That does seem a bit odd yes.... what value is [datecell] and WorksheetFunction.EoMonth([datecell], 3) if you Debug.Print or msgbox them?
– Sam
Nov 4 '13 at 12:56




That does seem a bit odd yes.... what value is [datecell] and WorksheetFunction.EoMonth([datecell], 3) if you Debug.Print or msgbox them?
– Sam
Nov 4 '13 at 12:56












6 Answers
6






active

oldest

votes


















14














Dates can be tricky with Excel VBA AutoFilter. Some find it easier to just loop through the array to be filtered.



Sometimes I have found that one can use the numeric value of the date, especially when dealing with "dates between"



Criteria1:= ">" & CDbl([datecell])
Criteria2:= "<=" & CDbl(WorksheetFunction.EoMonth([datecell], 3))


Note that the above need to be "real dates" and not strings that look like dates. Even a single "string date" will mess things up.






share|improve this answer























  • Yeah, tried that as well earlier and it didnt work either. It's a mystery to me. Especially given the fact that the recorded macro does not work either.
    – Spurious
    Nov 4 '13 at 12:48






  • 1




    Another option, when dealing with "dates between", is to use the Excel stored number. See my Edit in the answer
    – Ron Rosenfeld
    Nov 4 '13 at 12:59










  • Something else to check is that all of your dates are really dates, and not strings that happen to look like dates. Even a single "string date" in the list will mess things up.
    – Ron Rosenfeld
    Nov 4 '13 at 13:03












  • The Double Method worked, it seems.
    – Spurious
    Nov 4 '13 at 13:05










  • Edit your answer to only show the CDbl Version and I mark it as correct and vote it up.
    – Spurious
    Nov 4 '13 at 13:06



















9














Autofilter() works with 'universal' format yyyy-mm-dd, i.e.:



Criteria1:= ">" & Format([MY_DATE],"yyyy-mm-dd")
Criteria2:= "<=" & Format([MY_DATE],"yyyy-mm-dd")


It's better because Excel can't 'understand' it wrong . If you use mm/dd/yyyy or dd/mm/yyyy Excel can fit 02/jan as 01/feb.



see:
http://www.oaltd.co.uk/ExcelProgRef/Ch22/ProgRefCh22.htm




The Rules for Working with Excel (International Issue)




  1. When you have to convert numbers and dates to strings for passing to Excel (such as in criteria for AutoFilter or .Formula strings), always explicitly convert the data to a US-formatted string, using Trim(Str(MyNumber)), or the sNumToUS() function shown earlier, for all number and date types. Excel will then use it correctly and convert it to the local number/date formats.






Edit:



We can create an universal Function using Application.International like:



Sub MySub()
Select Case application.International(xlDateOrder)
Case Is = 0
dtFormat = "mm/dd/yyyy"
Case Is = 1
dtFormat = "dd/mm/yyyy"
Case Is = 2
dtFormat = "yyyy/mm/dd"
Case Else
dtFormat = "Error"
End Select

Debug.Print (dtFormat)

...
Criteria1:= ">" & Format([MY_DATE],dtFormat)
Criteria2:= "<=" & Format([MY_DATE],dtFormat)
...

End Sub





share|improve this answer































    2














    you need to convert the format to the american format, like:
    ">" & Format([datecell], "mm/dd/yyyy")
    VBA does not understand another format.






    share|improve this answer





























      1














      This syntax works for me:



      .AutoFilter Field:=2, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Now, "yyyy-mm-dd"))


      Hint obtained through a macro registration






      share|improve this answer





























        0














        Karlos Henrique,
        Thanks for suggesting Format([datecell], "mm/dd/yyyy").
        It works in my file.
        My previous code is :



        Range("A1:D" & Trim(Str(LstRec))).AutoFilter Field:=2, _
        Criteria1:=">=" & StrtDt, _
        Operator:=xlAnd, _
        Criteria2:="<=" & EndDt


        My modified code is :



        Range("A1:D" & Trim(Str(LstRec))).AutoFilter Field:=2, _
        Criteria1:=">=" & Format(StrtDt, "mm/dd/yyyy"), _
        Operator:=xlAnd, _
        Criteria2:="<=" & Format(EndDt, "mm/dd/yyyy")


        Thanks.






        share|improve this answer





























          0














          Match your "dd-mm-yyy" to the format of the column, so if you have "16-Aug-16" as your source data formatting then make the filter as "dd-mmm-yy"






          share|improve this answer





















          • This worked for me... my column was formatted as dd-mmmm-yyyy and that threw everything out. Changing the criteria fixed the issue.
            – SlowLearner
            Sep 23 '16 at 12:12











          Your Answer






          StackExchange.ifUsing("editor", function () {
          StackExchange.using("externalEditor", function () {
          StackExchange.using("snippets", function () {
          StackExchange.snippets.init();
          });
          });
          }, "code-snippets");

          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "1"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: true,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: 10,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f19767043%2fexcel-vba-autofilter-not-working-with-date-column%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          6 Answers
          6






          active

          oldest

          votes








          6 Answers
          6






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          14














          Dates can be tricky with Excel VBA AutoFilter. Some find it easier to just loop through the array to be filtered.



          Sometimes I have found that one can use the numeric value of the date, especially when dealing with "dates between"



          Criteria1:= ">" & CDbl([datecell])
          Criteria2:= "<=" & CDbl(WorksheetFunction.EoMonth([datecell], 3))


          Note that the above need to be "real dates" and not strings that look like dates. Even a single "string date" will mess things up.






          share|improve this answer























          • Yeah, tried that as well earlier and it didnt work either. It's a mystery to me. Especially given the fact that the recorded macro does not work either.
            – Spurious
            Nov 4 '13 at 12:48






          • 1




            Another option, when dealing with "dates between", is to use the Excel stored number. See my Edit in the answer
            – Ron Rosenfeld
            Nov 4 '13 at 12:59










          • Something else to check is that all of your dates are really dates, and not strings that happen to look like dates. Even a single "string date" in the list will mess things up.
            – Ron Rosenfeld
            Nov 4 '13 at 13:03












          • The Double Method worked, it seems.
            – Spurious
            Nov 4 '13 at 13:05










          • Edit your answer to only show the CDbl Version and I mark it as correct and vote it up.
            – Spurious
            Nov 4 '13 at 13:06
















          14














          Dates can be tricky with Excel VBA AutoFilter. Some find it easier to just loop through the array to be filtered.



          Sometimes I have found that one can use the numeric value of the date, especially when dealing with "dates between"



          Criteria1:= ">" & CDbl([datecell])
          Criteria2:= "<=" & CDbl(WorksheetFunction.EoMonth([datecell], 3))


          Note that the above need to be "real dates" and not strings that look like dates. Even a single "string date" will mess things up.






          share|improve this answer























          • Yeah, tried that as well earlier and it didnt work either. It's a mystery to me. Especially given the fact that the recorded macro does not work either.
            – Spurious
            Nov 4 '13 at 12:48






          • 1




            Another option, when dealing with "dates between", is to use the Excel stored number. See my Edit in the answer
            – Ron Rosenfeld
            Nov 4 '13 at 12:59










          • Something else to check is that all of your dates are really dates, and not strings that happen to look like dates. Even a single "string date" in the list will mess things up.
            – Ron Rosenfeld
            Nov 4 '13 at 13:03












          • The Double Method worked, it seems.
            – Spurious
            Nov 4 '13 at 13:05










          • Edit your answer to only show the CDbl Version and I mark it as correct and vote it up.
            – Spurious
            Nov 4 '13 at 13:06














          14












          14








          14






          Dates can be tricky with Excel VBA AutoFilter. Some find it easier to just loop through the array to be filtered.



          Sometimes I have found that one can use the numeric value of the date, especially when dealing with "dates between"



          Criteria1:= ">" & CDbl([datecell])
          Criteria2:= "<=" & CDbl(WorksheetFunction.EoMonth([datecell], 3))


          Note that the above need to be "real dates" and not strings that look like dates. Even a single "string date" will mess things up.






          share|improve this answer














          Dates can be tricky with Excel VBA AutoFilter. Some find it easier to just loop through the array to be filtered.



          Sometimes I have found that one can use the numeric value of the date, especially when dealing with "dates between"



          Criteria1:= ">" & CDbl([datecell])
          Criteria2:= "<=" & CDbl(WorksheetFunction.EoMonth([datecell], 3))


          Note that the above need to be "real dates" and not strings that look like dates. Even a single "string date" will mess things up.







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Nov 4 '13 at 13:21

























          answered Nov 4 '13 at 12:43









          Ron RosenfeldRon Rosenfeld

          22.8k41636




          22.8k41636












          • Yeah, tried that as well earlier and it didnt work either. It's a mystery to me. Especially given the fact that the recorded macro does not work either.
            – Spurious
            Nov 4 '13 at 12:48






          • 1




            Another option, when dealing with "dates between", is to use the Excel stored number. See my Edit in the answer
            – Ron Rosenfeld
            Nov 4 '13 at 12:59










          • Something else to check is that all of your dates are really dates, and not strings that happen to look like dates. Even a single "string date" in the list will mess things up.
            – Ron Rosenfeld
            Nov 4 '13 at 13:03












          • The Double Method worked, it seems.
            – Spurious
            Nov 4 '13 at 13:05










          • Edit your answer to only show the CDbl Version and I mark it as correct and vote it up.
            – Spurious
            Nov 4 '13 at 13:06


















          • Yeah, tried that as well earlier and it didnt work either. It's a mystery to me. Especially given the fact that the recorded macro does not work either.
            – Spurious
            Nov 4 '13 at 12:48






          • 1




            Another option, when dealing with "dates between", is to use the Excel stored number. See my Edit in the answer
            – Ron Rosenfeld
            Nov 4 '13 at 12:59










          • Something else to check is that all of your dates are really dates, and not strings that happen to look like dates. Even a single "string date" in the list will mess things up.
            – Ron Rosenfeld
            Nov 4 '13 at 13:03












          • The Double Method worked, it seems.
            – Spurious
            Nov 4 '13 at 13:05










          • Edit your answer to only show the CDbl Version and I mark it as correct and vote it up.
            – Spurious
            Nov 4 '13 at 13:06
















          Yeah, tried that as well earlier and it didnt work either. It's a mystery to me. Especially given the fact that the recorded macro does not work either.
          – Spurious
          Nov 4 '13 at 12:48




          Yeah, tried that as well earlier and it didnt work either. It's a mystery to me. Especially given the fact that the recorded macro does not work either.
          – Spurious
          Nov 4 '13 at 12:48




          1




          1




          Another option, when dealing with "dates between", is to use the Excel stored number. See my Edit in the answer
          – Ron Rosenfeld
          Nov 4 '13 at 12:59




          Another option, when dealing with "dates between", is to use the Excel stored number. See my Edit in the answer
          – Ron Rosenfeld
          Nov 4 '13 at 12:59












          Something else to check is that all of your dates are really dates, and not strings that happen to look like dates. Even a single "string date" in the list will mess things up.
          – Ron Rosenfeld
          Nov 4 '13 at 13:03






          Something else to check is that all of your dates are really dates, and not strings that happen to look like dates. Even a single "string date" in the list will mess things up.
          – Ron Rosenfeld
          Nov 4 '13 at 13:03














          The Double Method worked, it seems.
          – Spurious
          Nov 4 '13 at 13:05




          The Double Method worked, it seems.
          – Spurious
          Nov 4 '13 at 13:05












          Edit your answer to only show the CDbl Version and I mark it as correct and vote it up.
          – Spurious
          Nov 4 '13 at 13:06




          Edit your answer to only show the CDbl Version and I mark it as correct and vote it up.
          – Spurious
          Nov 4 '13 at 13:06













          9














          Autofilter() works with 'universal' format yyyy-mm-dd, i.e.:



          Criteria1:= ">" & Format([MY_DATE],"yyyy-mm-dd")
          Criteria2:= "<=" & Format([MY_DATE],"yyyy-mm-dd")


          It's better because Excel can't 'understand' it wrong . If you use mm/dd/yyyy or dd/mm/yyyy Excel can fit 02/jan as 01/feb.



          see:
          http://www.oaltd.co.uk/ExcelProgRef/Ch22/ProgRefCh22.htm




          The Rules for Working with Excel (International Issue)




          1. When you have to convert numbers and dates to strings for passing to Excel (such as in criteria for AutoFilter or .Formula strings), always explicitly convert the data to a US-formatted string, using Trim(Str(MyNumber)), or the sNumToUS() function shown earlier, for all number and date types. Excel will then use it correctly and convert it to the local number/date formats.






          Edit:



          We can create an universal Function using Application.International like:



          Sub MySub()
          Select Case application.International(xlDateOrder)
          Case Is = 0
          dtFormat = "mm/dd/yyyy"
          Case Is = 1
          dtFormat = "dd/mm/yyyy"
          Case Is = 2
          dtFormat = "yyyy/mm/dd"
          Case Else
          dtFormat = "Error"
          End Select

          Debug.Print (dtFormat)

          ...
          Criteria1:= ">" & Format([MY_DATE],dtFormat)
          Criteria2:= "<=" & Format([MY_DATE],dtFormat)
          ...

          End Sub





          share|improve this answer




























            9














            Autofilter() works with 'universal' format yyyy-mm-dd, i.e.:



            Criteria1:= ">" & Format([MY_DATE],"yyyy-mm-dd")
            Criteria2:= "<=" & Format([MY_DATE],"yyyy-mm-dd")


            It's better because Excel can't 'understand' it wrong . If you use mm/dd/yyyy or dd/mm/yyyy Excel can fit 02/jan as 01/feb.



            see:
            http://www.oaltd.co.uk/ExcelProgRef/Ch22/ProgRefCh22.htm




            The Rules for Working with Excel (International Issue)




            1. When you have to convert numbers and dates to strings for passing to Excel (such as in criteria for AutoFilter or .Formula strings), always explicitly convert the data to a US-formatted string, using Trim(Str(MyNumber)), or the sNumToUS() function shown earlier, for all number and date types. Excel will then use it correctly and convert it to the local number/date formats.






            Edit:



            We can create an universal Function using Application.International like:



            Sub MySub()
            Select Case application.International(xlDateOrder)
            Case Is = 0
            dtFormat = "mm/dd/yyyy"
            Case Is = 1
            dtFormat = "dd/mm/yyyy"
            Case Is = 2
            dtFormat = "yyyy/mm/dd"
            Case Else
            dtFormat = "Error"
            End Select

            Debug.Print (dtFormat)

            ...
            Criteria1:= ">" & Format([MY_DATE],dtFormat)
            Criteria2:= "<=" & Format([MY_DATE],dtFormat)
            ...

            End Sub





            share|improve this answer


























              9












              9








              9






              Autofilter() works with 'universal' format yyyy-mm-dd, i.e.:



              Criteria1:= ">" & Format([MY_DATE],"yyyy-mm-dd")
              Criteria2:= "<=" & Format([MY_DATE],"yyyy-mm-dd")


              It's better because Excel can't 'understand' it wrong . If you use mm/dd/yyyy or dd/mm/yyyy Excel can fit 02/jan as 01/feb.



              see:
              http://www.oaltd.co.uk/ExcelProgRef/Ch22/ProgRefCh22.htm




              The Rules for Working with Excel (International Issue)




              1. When you have to convert numbers and dates to strings for passing to Excel (such as in criteria for AutoFilter or .Formula strings), always explicitly convert the data to a US-formatted string, using Trim(Str(MyNumber)), or the sNumToUS() function shown earlier, for all number and date types. Excel will then use it correctly and convert it to the local number/date formats.






              Edit:



              We can create an universal Function using Application.International like:



              Sub MySub()
              Select Case application.International(xlDateOrder)
              Case Is = 0
              dtFormat = "mm/dd/yyyy"
              Case Is = 1
              dtFormat = "dd/mm/yyyy"
              Case Is = 2
              dtFormat = "yyyy/mm/dd"
              Case Else
              dtFormat = "Error"
              End Select

              Debug.Print (dtFormat)

              ...
              Criteria1:= ">" & Format([MY_DATE],dtFormat)
              Criteria2:= "<=" & Format([MY_DATE],dtFormat)
              ...

              End Sub





              share|improve this answer














              Autofilter() works with 'universal' format yyyy-mm-dd, i.e.:



              Criteria1:= ">" & Format([MY_DATE],"yyyy-mm-dd")
              Criteria2:= "<=" & Format([MY_DATE],"yyyy-mm-dd")


              It's better because Excel can't 'understand' it wrong . If you use mm/dd/yyyy or dd/mm/yyyy Excel can fit 02/jan as 01/feb.



              see:
              http://www.oaltd.co.uk/ExcelProgRef/Ch22/ProgRefCh22.htm




              The Rules for Working with Excel (International Issue)




              1. When you have to convert numbers and dates to strings for passing to Excel (such as in criteria for AutoFilter or .Formula strings), always explicitly convert the data to a US-formatted string, using Trim(Str(MyNumber)), or the sNumToUS() function shown earlier, for all number and date types. Excel will then use it correctly and convert it to the local number/date formats.






              Edit:



              We can create an universal Function using Application.International like:



              Sub MySub()
              Select Case application.International(xlDateOrder)
              Case Is = 0
              dtFormat = "mm/dd/yyyy"
              Case Is = 1
              dtFormat = "dd/mm/yyyy"
              Case Is = 2
              dtFormat = "yyyy/mm/dd"
              Case Else
              dtFormat = "Error"
              End Select

              Debug.Print (dtFormat)

              ...
              Criteria1:= ">" & Format([MY_DATE],dtFormat)
              Criteria2:= "<=" & Format([MY_DATE],dtFormat)
              ...

              End Sub






              share|improve this answer














              share|improve this answer



              share|improve this answer








              edited Sep 21 '15 at 16:21

























              answered Aug 6 '15 at 21:13









              MakahMakah

              2,75123153




              2,75123153























                  2














                  you need to convert the format to the american format, like:
                  ">" & Format([datecell], "mm/dd/yyyy")
                  VBA does not understand another format.






                  share|improve this answer


























                    2














                    you need to convert the format to the american format, like:
                    ">" & Format([datecell], "mm/dd/yyyy")
                    VBA does not understand another format.






                    share|improve this answer
























                      2












                      2








                      2






                      you need to convert the format to the american format, like:
                      ">" & Format([datecell], "mm/dd/yyyy")
                      VBA does not understand another format.






                      share|improve this answer












                      you need to convert the format to the american format, like:
                      ">" & Format([datecell], "mm/dd/yyyy")
                      VBA does not understand another format.







                      share|improve this answer












                      share|improve this answer



                      share|improve this answer










                      answered May 22 '14 at 15:12









                      Karlos HenriqueKarlos Henrique

                      211




                      211























                          1














                          This syntax works for me:



                          .AutoFilter Field:=2, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Now, "yyyy-mm-dd"))


                          Hint obtained through a macro registration






                          share|improve this answer


























                            1














                            This syntax works for me:



                            .AutoFilter Field:=2, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Now, "yyyy-mm-dd"))


                            Hint obtained through a macro registration






                            share|improve this answer
























                              1












                              1








                              1






                              This syntax works for me:



                              .AutoFilter Field:=2, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Now, "yyyy-mm-dd"))


                              Hint obtained through a macro registration






                              share|improve this answer












                              This syntax works for me:



                              .AutoFilter Field:=2, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Now, "yyyy-mm-dd"))


                              Hint obtained through a macro registration







                              share|improve this answer












                              share|improve this answer



                              share|improve this answer










                              answered Apr 29 '16 at 14:35









                              Mago FabianMago Fabian

                              111




                              111























                                  0














                                  Karlos Henrique,
                                  Thanks for suggesting Format([datecell], "mm/dd/yyyy").
                                  It works in my file.
                                  My previous code is :



                                  Range("A1:D" & Trim(Str(LstRec))).AutoFilter Field:=2, _
                                  Criteria1:=">=" & StrtDt, _
                                  Operator:=xlAnd, _
                                  Criteria2:="<=" & EndDt


                                  My modified code is :



                                  Range("A1:D" & Trim(Str(LstRec))).AutoFilter Field:=2, _
                                  Criteria1:=">=" & Format(StrtDt, "mm/dd/yyyy"), _
                                  Operator:=xlAnd, _
                                  Criteria2:="<=" & Format(EndDt, "mm/dd/yyyy")


                                  Thanks.






                                  share|improve this answer


























                                    0














                                    Karlos Henrique,
                                    Thanks for suggesting Format([datecell], "mm/dd/yyyy").
                                    It works in my file.
                                    My previous code is :



                                    Range("A1:D" & Trim(Str(LstRec))).AutoFilter Field:=2, _
                                    Criteria1:=">=" & StrtDt, _
                                    Operator:=xlAnd, _
                                    Criteria2:="<=" & EndDt


                                    My modified code is :



                                    Range("A1:D" & Trim(Str(LstRec))).AutoFilter Field:=2, _
                                    Criteria1:=">=" & Format(StrtDt, "mm/dd/yyyy"), _
                                    Operator:=xlAnd, _
                                    Criteria2:="<=" & Format(EndDt, "mm/dd/yyyy")


                                    Thanks.






                                    share|improve this answer
























                                      0












                                      0








                                      0






                                      Karlos Henrique,
                                      Thanks for suggesting Format([datecell], "mm/dd/yyyy").
                                      It works in my file.
                                      My previous code is :



                                      Range("A1:D" & Trim(Str(LstRec))).AutoFilter Field:=2, _
                                      Criteria1:=">=" & StrtDt, _
                                      Operator:=xlAnd, _
                                      Criteria2:="<=" & EndDt


                                      My modified code is :



                                      Range("A1:D" & Trim(Str(LstRec))).AutoFilter Field:=2, _
                                      Criteria1:=">=" & Format(StrtDt, "mm/dd/yyyy"), _
                                      Operator:=xlAnd, _
                                      Criteria2:="<=" & Format(EndDt, "mm/dd/yyyy")


                                      Thanks.






                                      share|improve this answer












                                      Karlos Henrique,
                                      Thanks for suggesting Format([datecell], "mm/dd/yyyy").
                                      It works in my file.
                                      My previous code is :



                                      Range("A1:D" & Trim(Str(LstRec))).AutoFilter Field:=2, _
                                      Criteria1:=">=" & StrtDt, _
                                      Operator:=xlAnd, _
                                      Criteria2:="<=" & EndDt


                                      My modified code is :



                                      Range("A1:D" & Trim(Str(LstRec))).AutoFilter Field:=2, _
                                      Criteria1:=">=" & Format(StrtDt, "mm/dd/yyyy"), _
                                      Operator:=xlAnd, _
                                      Criteria2:="<=" & Format(EndDt, "mm/dd/yyyy")


                                      Thanks.







                                      share|improve this answer












                                      share|improve this answer



                                      share|improve this answer










                                      answered Jun 14 '14 at 5:59









                                      pmykpmyk

                                      11




                                      11























                                          0














                                          Match your "dd-mm-yyy" to the format of the column, so if you have "16-Aug-16" as your source data formatting then make the filter as "dd-mmm-yy"






                                          share|improve this answer





















                                          • This worked for me... my column was formatted as dd-mmmm-yyyy and that threw everything out. Changing the criteria fixed the issue.
                                            – SlowLearner
                                            Sep 23 '16 at 12:12
















                                          0














                                          Match your "dd-mm-yyy" to the format of the column, so if you have "16-Aug-16" as your source data formatting then make the filter as "dd-mmm-yy"






                                          share|improve this answer





















                                          • This worked for me... my column was formatted as dd-mmmm-yyyy and that threw everything out. Changing the criteria fixed the issue.
                                            – SlowLearner
                                            Sep 23 '16 at 12:12














                                          0












                                          0








                                          0






                                          Match your "dd-mm-yyy" to the format of the column, so if you have "16-Aug-16" as your source data formatting then make the filter as "dd-mmm-yy"






                                          share|improve this answer












                                          Match your "dd-mm-yyy" to the format of the column, so if you have "16-Aug-16" as your source data formatting then make the filter as "dd-mmm-yy"







                                          share|improve this answer












                                          share|improve this answer



                                          share|improve this answer










                                          answered Aug 16 '16 at 8:28









                                          Graham PGraham P

                                          11




                                          11












                                          • This worked for me... my column was formatted as dd-mmmm-yyyy and that threw everything out. Changing the criteria fixed the issue.
                                            – SlowLearner
                                            Sep 23 '16 at 12:12


















                                          • This worked for me... my column was formatted as dd-mmmm-yyyy and that threw everything out. Changing the criteria fixed the issue.
                                            – SlowLearner
                                            Sep 23 '16 at 12:12
















                                          This worked for me... my column was formatted as dd-mmmm-yyyy and that threw everything out. Changing the criteria fixed the issue.
                                          – SlowLearner
                                          Sep 23 '16 at 12:12




                                          This worked for me... my column was formatted as dd-mmmm-yyyy and that threw everything out. Changing the criteria fixed the issue.
                                          – SlowLearner
                                          Sep 23 '16 at 12:12


















                                          draft saved

                                          draft discarded




















































                                          Thanks for contributing an answer to Stack Overflow!


                                          • Please be sure to answer the question. Provide details and share your research!

                                          But avoid



                                          • Asking for help, clarification, or responding to other answers.

                                          • Making statements based on opinion; back them up with references or personal experience.


                                          To learn more, see our tips on writing great answers.





                                          Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                                          Please pay close attention to the following guidance:


                                          • Please be sure to answer the question. Provide details and share your research!

                                          But avoid



                                          • Asking for help, clarification, or responding to other answers.

                                          • Making statements based on opinion; back them up with references or personal experience.


                                          To learn more, see our tips on writing great answers.




                                          draft saved


                                          draft discarded














                                          StackExchange.ready(
                                          function () {
                                          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f19767043%2fexcel-vba-autofilter-not-working-with-date-column%23new-answer', 'question_page');
                                          }
                                          );

                                          Post as a guest















                                          Required, but never shown





















































                                          Required, but never shown














                                          Required, but never shown












                                          Required, but never shown







                                          Required, but never shown

































                                          Required, but never shown














                                          Required, but never shown












                                          Required, but never shown







                                          Required, but never shown







                                          Popular posts from this blog

                                          Monofisismo

                                          Angular Downloading a file using contenturl with Basic Authentication

                                          Olmecas