How to set multiple criteria for .advancedfilter in VBA

Hello All,

I am trying to use the xlfiltercopy action with the .advancedfilter function in vba to filter through some data and copy it to another sheet. The criteria for the filter, however, will be all values between two variable values that will change on every iteration of the filter, i.e a 'continuous' data set so printing all the criteria onto a range is not possible. Consequently, as the .advancedfilter function doesn't seem to allow for criteria1, criteria2, etc.. like .autofilter, I need a way to set the criteriarange to a 'between' function, i.e. x
The code I have so far is below.

Thanks!

Dorian

Please Login or Register  to view this content.

Dorian

It is possible to set a 'between' criteria for advanced filter in a range.

In the range you would repeat the field twice in the header row and below you would have your ">=" & crit1lo and "<=" & crit1hi.

Try doing it manually to see what I mean.

PS Can you add code tags when posting code?

Thanks, What do you mean repeat the field twice in the header row?

I've tried what I think you are suggesting, and am receiving the following error at the line for the advanced filter


Method 'range' of object '_Global' failed

Please Login or Register  to view this content.

The criteria range needs to be on a worksheet, you can't 'create' it in the code.

You can however populate the criteria range using code.