可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效,请关闭广告屏蔽插件后再试):
问题:
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.
回答1:
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.
回答2:
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
回答3:
you need to convert the format to the american format, like:
">" & Format([datecell], "mm/dd/yyyy")
VBA does not understand another format.
回答4:
This syntax works for me:
.AutoFilter Field:=2, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Now, "yyyy-mm-dd"))
Hint obtained through a macro registration
回答5:
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.
回答6:
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"
回答7:
One solution is to set up correctly the format of the target column. If its formatted as "custom", even if it looks like a date, it won't work.
You are asking VBA to filter based on a date, therefore the format of the columns has to be a date too.
回答8:
SO this worked for me pretty clean
ActiveSheet.Range("$A$1:$K$35727").AutoFilter Field:=1, Criteria1:= _
">=" & Range("G1"), Operator:=xlAnd, Criteria2:="<=" & Range("H1")
You can try this as well
Expected output will start date in my G1 cell and end date will be H1 cell.
回答9:
here's the occam's razor solution... try putting this in Autoopen for the spreadsheet or if you need to, modify it for the sheet that you wish to affect. it will cause the drop down filters for the date headers to appear as individual dates and not as a date hierarchy.
ActiveWindow.AutoFilterDateGrouping = False