first af all i'm glad to enjoy this nice community, this is my first post so apologize me in advance for any mistake i'll do in the post.
I got a table named example generated by importing data from an access file, so the number of rows changes dynamically...I'm trying to apply the AdvancedFilter method to this table via VBA.
I've used the first row of the sheet for copying the header of the table and in the second row i set my parameters for filtering, so in the range A1:D2 i got the criteria. Starting from row 5 i have the table example: it starts with a header row and goes on with all the rows of data, all imported from the access file, for example a range A5:D20.
If i apply the advanced filter manually (click on the advanced filter button and fill the form) specifying the range of the table A5:D30 and the range of the criteria A1:D2 it works fine...but if instead of specifying the range of the data i use the name of the table example the result is different, just one row instead of the five (right) rows i expect...why?!?
Now, if the question above is just for curiosity (but i'm sure it's connected with my problem), the main problem is that i'm trying to do the same thing via VBA code. I tried
Worksheets("name").Range("example[#All]").AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:=Range("A1:D2"), Unique:=False
and
Worksheets("name").ListObjects("example").Range.AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:=Range("A1:D2")
but both don't work: the filtering action didn't return no lines.
Even if i try
Worksheets("name").Range("A5:D30").AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:=Range("A1:D2"), Unique:=False
specifying manually the range (not a solution for my problem because the range changes dynamically, but for test purpose...) the problem persists, the code didn't return no lines.
Note: if i select the range in the codes above instead of AdvancedFilter it the selection is correct: the entire table with the header
The first code is the same automatically written if i start recording a macro, do the filtering manually, and stop recording the macro (code written exactly with the table reference)...the wierd thing is that when i manually do the operation (while recording the macro) it filters correctly, but if i start the macro just recorded...nothing, same as if i run the code written above.
If i open the formula->names tab i can find the example table but the range specifyed is not starting from row 5 but from 6...it's like the header is not considered, don't know why...but it's automatically generated with the importation of the data so i don't know how to change the range keeping the reference to the imported data...maybe is this the issue?!?
Don't know how to solve the problem, please help me :)
Edit:
Two of the criteria i'm using for filter are date criteria, specifically: date <= xx/yy/zzzz and date >= aa/bb/cccc and the problem it's exactly this...if i try the three codes above filtering with a string criteria, they works, if i use them with the dates criteria, they don't. So i guess the problem depends on how VBA hands the dates data types. Any suggestion?
The cells used for criteria contain this formula ("SE" is the italian version of "IF"):
=SE(filtro!F2="";"";">="&filtro!F2)
=SE(filtro!F3="";"";"<="&filtro!F3)
i take the criteria from another sheet ("filtro") and if the cell is filled i report the data inserted with <= or >= for the criteria, otherwise the cell remains empty for don't set any condition.
Thanks
Ettore