How to set Filter.Criteria1 by code?

Hi,

Problem statement:
I'm trying to set the criteria1 of a Filter but I get error message 'Object required', cannot get around it.

Please Login or Register  to view this content.

Background:
I've been using

Please Login or Register  to view this content.

That works OK, but since Excel 2007 it slows down my application by much so users are complaining. So I'd like to make it faster by trying to get the above code work.
Previously my code started with switching off all filtering criterias and then setting the desired new filters.
The problem with this is that time is consumed on filters set to the same criterias than it was before. I would like the code to select which new criterias are the same and do not re-do the stting on those columns.

Goal:
There are 11 filtering criterias that are being selected by the User.
The code should go through the AutoFilter area and take each column that needs to be filtered.
Look whether the user-set filter is the same or not that is already set.
If same, do nothing, if different re-set the Criteria1.

Not sure it is clear.
Would appreciate any help.

Hi Gabor,

I had a problem a few months ago that I called my Dell Parts Filter Problem. The Dell Parts guy had over 9000 parts he needed to find fast. I created the attached file for him.

The idea was he could double click on any cell below the yellow cells and it would use that word under the Green Cells which are a Criteria Range for an advanced filter in the data. To clear the filtered word he would click on a blank cell under the yellow cells. The event macro would build a unique list of what was still available below the yellow cells with each double click.

Example. He needs the part number for the Studio Portable 1555 Memory
1. He double clicks in Col K on Studio Portable.
2. He then double clicks in Col L on 1555 - Hanks
3. He last double clicks in Col M on Memory.

This then shows the list of matching parts (of the 9000 plus) parts in Cols A to H.

To undo this he simply double clicks on blank cells below the words in Cols K to M

This seemed to be a very fast way to filter things. Perhaps you can apply this method to your problem to speed things up.

Hi MarvinP, can you attach an example? Thanks

I attached a sample after I trimmed it down to fit. Let me know if you can't get it. I took me 4 minutes after posting the text to get the attachment up there. See the attached in my first post.

Sorry for the confusion.

Thanks I got it. Pretty cool.
I'm sorry, I'm afraid I cannot use it.
Basically I'd like to have the actual filters(column).criteria1 property returned in to a variable and then compared with the desired filters for the respective columns, and only activate the filtering procedure when there is a different filter desired than the one that is already set.

I guess it's time to build us a Sample File so we can see what you are talking about.

It sounds like you can simply Concatenate the Filter Criteria and compare it to what was there last. If different then run the advanced filter.

How about a sample that shows a table with only 4 criteria and the layout you are looking at.

I have solved it in the meantime.
Effect: runtime dropped from 40sec to 8-40sec dependant on how many criterias are changed .

The first step is I set a variable with the actual filter criterias:

Please Login or Register  to view this content.

Then I compare the members of this variable column by column with the desired filter tha tis to be set and activate new filter only it it is different. (sorry for the code is not general but rather specific)

Please Login or Register  to view this content.