Macro error help AutoFilter searching 2 columns

[rik130]

Hello.

I am using this AutoFilter macro I found from another site. I want to add a 2nd filter specifically for column 2. I have tried copying the code and making a new sub, adjusting the cell values to suit and the filterrange to 2, but I get "Complie error : ambiguous name detected : Worksheet_Change". I have tried renaming them but as soon as I rename the first one to anything different it stops working. Is it possible to combine the 2 search requirements into the 1 sub?

Please Login or Register  to view this content.

That works OK.

This below does not.

Please Login or Register  to view this content.

Thank you.

[Logit]

.
There can be only one "Private Sub Worksheet_Change(ByVal Target As Range)"

Combine both macros into one :

Please Login or Register  to view this content.

ps: The above is a quick response. You may need to rename the variables in the second macro to be unique from those in the first macro or delete some of the variables like the DIM statements in the second macro.

[rik130]

Thanks Logit. Unfortunately that doesn't work.

"Compile Error : Duplicate declaration in current scope."

[Logit]

.
This works here BUT it will only filter one column at a time. You can filter Col A ... then if you want to filter Col B, you will need to unfilter Col A.

Please Login or Register  to view this content.

Note how the variables in the second macro are all named differently from the first macro. That is why you were getting the last error message.

[rik130]

Thanks Logit, unfortunately that doesn't work either.

"Run-time error 1004. AutoFilter method of Range class failed" and highlights this section of the code :

Please Login or Register  to view this content.

[jeffreybrown]

Hi rik30,

Before we proceed with this thread, please read the forum rules about proper thread titles and using code tags. Thanks!

[rik130]

I can't seem to attach the xlsm, when I click the 'attach' button I just get an empty white box even though I've uploaded it in the 'advanced' screen. I got the demo file from here if you want to look it to see what may be causing the error : teachexcel.com/excel-tutorial/2205/filter-data-as-you-type-in-excel?nav=yt .

Jeff sorry for not using the code tag in my previous post, I've changed it now.

[jeffreybrown]

Thank you rik130 for adding the code tags, but please do something to your thread title as well.

Administrative Note:

Welcome to the forum.

We would love to help you with your query; however, the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[rik130]

I've changed it twice already. I don't know how many more times you want me to change it? Perhaps you could change it to something that meets with your requirements as clearly my efforts are not good enough.

Could someone answer my query please!

[Logit]

.
rik130 :

Tested and works here. Apologies for the oversight ...

Please Login or Register  to view this content.

[rik130]

Thanks logit, unfortunately that still doesn't work. Filter on column 1 works fine but when clearing it and entering a value from column 2 in B2, it hides all records, including the row you are filtering for.

[Logit]

.
Ok.

What is the goal you are expecting ?

It is late here now and I am off to sleep. Will check in the morning.

Cheers.

[rik130]

As stated, there is no filtering on column 2. If the filter for column 1 is empty and you enter some search criteria for data in column 2, all it does is hides all rows, including any that match the filter criteria. In short, column 2 filter does nothing other than hide all records. Can column 2 be made to show records that match criteria entered in the filter?

[Logit]

.
If I enter "B8" for cell B2, then press ENTER, it displays cells B2, B4 and B8. The macro is performing as it is designed based on the
current logic.

If you edit this line : Set filterRange2 = Range("B4:B16") to this : Set filterRange2 = Range("B3:B16") then
the only cells displayed (based on the example above), will be cells B2 and B8. Again, this is how the filter is designed to work.

If this doesn't solve the issue, please provide additional information.

Thanks.

[Vraag en antwoord]

Place an example of your file.

[Logit]

.
Here is a copy of my workbook example for you to review. Does mine function the same as yours ?
If not ... what differences are there ?

[Vraag en antwoord]

@Logit, I ask the TS for an example file. With almost 5000 posts you can't come up with something better than the example?

Please Login or Register  to view this content.

[Logit]

.
Vraag en antwoord :

Once again, ignorance and insolence has raised its ugly head, then voiced a personal opinion which is unsolicited and unwarranted.


"@Logit, I ask the TS for an example file." Yes you did ... but you presume too much. Perhaps with slight consideration on your part
you might realize my workbook copy was provided for the OP to review ... irrespective of your request. Thank you for suggesting the OP post HIS
workbook copy for review as that prompted me to provide my copy for him to review in hopes it would assist him.


"With almost 5000 posts you can't come up with something better than the example?" And by what authority do you presume to judge
any else's skills ?


Let me enlighten you why I did not change the OP's original code. An OP is familiar with what they are doing ... not what someone else understands who
has a different skill set. Rather than confuse an OP with something totally different I believe it is always best to provide them guidance and/or an answer
based on their existing code ... if possible.

In the past I learned early on when I completely changed an OP's original code the most often immediate response was "What was wrong with my code ?" So ...
I stopped changing someone else's code if ... the code they provided worked with some tweaking.

You as an additional contributor may always add to the OP's knowledge level by providing a different method. HOWEVER, when this is done it is ALWAYS best
to provide the OP with a discussion / comments of what the new code is doing and why. Otherwise, what is the point ?

Today's world is overrun with 'holier than thou' critics, nervous with anticipation of who their next victim will be. I for one am sick and tired of all of it and will
call it out - for what it is - when it occurs - in as nice a manner as possible.

Thank you for volunteering your efforts for the OP's benefit. You are however, not welcome to display your ignorance and holier mindset towards others.

May you have a wonderful day and Merry Christmas / Happy New Year !

Cheers.

[Logit]

.
rik130 :

May I offer you my sincere apologies for your experiences so far on this FORUM ? Please don't judge these occurrences as "the norm" of how things
work here.

All of this is an exception to the rule.

Again, please accept my apologies.