Excel VBA Autofilter not working with Date column
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
|
show 4 more comments
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
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 asCDate([datecell])
change toFormat([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]
andWorksheetFunction.EoMonth([datecell], 3)
if youDebug.Print
ormsgbox
them?
– Sam
Nov 4 '13 at 12:56
|
show 4 more comments
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
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
excel vba date autofilter
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 asCDate([datecell])
change toFormat([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]
andWorksheetFunction.EoMonth([datecell], 3)
if youDebug.Print
ormsgbox
them?
– Sam
Nov 4 '13 at 12:56
|
show 4 more comments
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 asCDate([datecell])
change toFormat([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]
andWorksheetFunction.EoMonth([datecell], 3)
if youDebug.Print
ormsgbox
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
|
show 4 more comments
6 Answers
6
active
oldest
votes
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.
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
|
show 5 more comments
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)
- 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
add a comment |
you need to convert the format to the american format, like:
">" & Format([datecell], "mm/dd/yyyy")
VBA does not understand another format.
add a comment |
This syntax works for me:
.AutoFilter Field:=2, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Now, "yyyy-mm-dd"))
Hint obtained through a macro registration
add a comment |
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.
add a comment |
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"
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
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
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
|
show 5 more comments
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.
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
|
show 5 more comments
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.
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.
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
|
show 5 more comments
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
|
show 5 more comments
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)
- 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
add a comment |
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)
- 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
add a comment |
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)
- 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
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)
- 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
edited Sep 21 '15 at 16:21
answered Aug 6 '15 at 21:13
MakahMakah
2,75123153
2,75123153
add a comment |
add a comment |
you need to convert the format to the american format, like:
">" & Format([datecell], "mm/dd/yyyy")
VBA does not understand another format.
add a comment |
you need to convert the format to the american format, like:
">" & Format([datecell], "mm/dd/yyyy")
VBA does not understand another format.
add a comment |
you need to convert the format to the american format, like:
">" & Format([datecell], "mm/dd/yyyy")
VBA does not understand another format.
you need to convert the format to the american format, like:
">" & Format([datecell], "mm/dd/yyyy")
VBA does not understand another format.
answered May 22 '14 at 15:12
Karlos HenriqueKarlos Henrique
211
211
add a comment |
add a comment |
This syntax works for me:
.AutoFilter Field:=2, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Now, "yyyy-mm-dd"))
Hint obtained through a macro registration
add a comment |
This syntax works for me:
.AutoFilter Field:=2, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Now, "yyyy-mm-dd"))
Hint obtained through a macro registration
add a comment |
This syntax works for me:
.AutoFilter Field:=2, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Now, "yyyy-mm-dd"))
Hint obtained through a macro registration
This syntax works for me:
.AutoFilter Field:=2, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Now, "yyyy-mm-dd"))
Hint obtained through a macro registration
answered Apr 29 '16 at 14:35
Mago FabianMago Fabian
111
111
add a comment |
add a comment |
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.
add a comment |
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.
add a comment |
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.
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.
answered Jun 14 '14 at 5:59
pmykpmyk
11
11
add a comment |
add a comment |
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"
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
add a comment |
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"
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
add a comment |
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"
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"
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
add a comment |
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
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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 toFormat([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]
andWorksheetFunction.EoMonth([datecell], 3)
if youDebug.Print
ormsgbox
them?– Sam
Nov 4 '13 at 12:56