VBA code for autofilter based on cell values

[Ateeb Ali]

Dear Sir, I am attaching a sample file.
I need VB code to autofilter data, it should work in same way as autofilter normally works.
For example, when we type text in B3 (merge cells) zzz, it should filter all following zzz
and then if we type text in B5 (merge cells) 7066, it should filter that data, it we type anything not there, it should show blank
Need urgent help please

 

[vletm]

Ateeb Ali
If so urgent (what ever that would be)
How next would work ?
it we type anything not there, it should show blank
If ... do not type anything ... then how do 'autofilter' know to show blank?

 

[Ateeb Ali]

Sir, it is same as autofilter works, like when you select one filter, it would show data of that filer
then we use other sub filter, it will filter out more data as per selection, I just need same thing on the sheet when user type in cell, the data will be filter accordingly.
May be I am not good in explanation but if you need more example, I can work out on sheet and re attached if needed

 

[vletm]

Ateeb Ali
Question > Answer

You could test this ...
there are two options to do that.

 

[Ateeb Ali]

Thanks a lot and really perfect, just one more help, is it possible we type less and it still show the result
for example, we need to type whole

BERMUDA BELT CHINO DENIM If we type just BERMUDA, IT SHOULD ALSO SHOW FILTER RESULT, YOU CAN REMOVE CLICKING OPTION, ITS NOT NEEDED

 

[vletm]

Ateeb Ali
Those 'one more help'-phrases ... means ... that You don't want after this any help ... hmm?
a) We do not need to type .. but You wanted so?
I can modify that You need to write as below .. and ... You could get something ... okay?

b) Clicking option is more useful than above typing-something!
I cannot remove it!

 

[Ateeb Ali]

Sir, it is now perfect but for following;

b) Clicking option is more useful than above typing-something!
I cannot remove it!

I want to elaborate that we need to add up lots of data to this table so I am getting problem in typing and adding data since VB code filtering it again and again

 

[vletm]

Ateeb Ali
Your original 'need urgent' has none mention of typing otherwhere!
Now, You can 'switch' it ON/OFF with [ SHORTCUT ]-option.

 

[Ateeb Ali]

Thanks sir, now all done
Really nice of you, regards

 

[Ateeb Ali]

Dear vietm
I need your help again, is it possible to show values in filter on fields like an example I have shown in attached file with data validation in cell B3.
The issue with data validation is that its showing repeated values, I need it to show only unique value, for example: "zzz and bbb"
Similarly sub filters in other cells (B5, B7, F3, F5, F7, I3, I5) should also worked according to following data from range A11:I35
Basically best alternative I need autofilter link on each cell same which showing in range: A10:I10

 

[vletm]

Ateeb Ali
That is possible, but ...
if there would be more than 12 unique values per criteria - especially the 1st selected criteria then that won't be useful!
... that depends, if there would be more rows than those 25.
... and all those 'dropdown' should solve AFTER You have selected criteria which You would like to use.

That's why - there is now that other option to select criteria manually.
... and ...
there is already - Your named option ready - basic Excel - FILTER.

 

[Ateeb Ali]

Dear Sir
I am attaching actual file
I have two options:
(1) I wanted to use your technique here but then I need to remove combo box and then need to do data validation.
(2) I wanted to use code of clear all, when we click, it should clear all selected filer.

Please help me in any of above one point, thanks a lot in advance

 

[vletm]

Ateeb Ali
Did You read my previous reply?
if there would be more than 12 unique values per criteria - especially the 1st selected criteria then that won't be useful!
There seems to be MANY columns which are not useful at all!
Term 'not useful' ... means ... as it has written ... and ... it is opposite of useful!

bonus:
You file gives next:


I could use same as I've given my sample.

 

[Ateeb Ali]

Dear Sir
Attaching file again in binary format, may be now its accessible

Yes first column has max. of 06 values (well under 12)
I need vb code to remove text from all combobox, need your help here as well.


Regards

 

[vletm]

Ateeb Ali
Did You read my previous reply?
( the 1st and the last lines )
.. and ...
it won't change anything, if offer Your file with .xlsb-format!
> It no need any VBA or any code to remove text from any combobox - I would use 'cut' or 'delete'.

 

[Ateeb Ali]

Dear Sir
I am sorry, I am not understanding this;

"there is already - Your named option ready - basic Excel - FILTER."

I know the file already have filter and you want me to use this, but I am giving lock version of file to user
They are using combobox to filer data, everything is now okay, I just need one code now, when user click clear all, it clear all filter in combo box, is i possible?

 

[vletm]

Ateeb Ali
If You have written so less details of Your tread which give a lot of freedom to give replies.
As You should know, basic Excel FILTERing is possible to use
while file is lock (maybe You mean, protected).

if there would be more than 12 unique values per criteria - then that won't be useful!!
and there should always solve those unique values for each .. eg combobox ... while select it. Those Your 'ready' list won't work.

Below code clears FILTERing

Sub clearF()
    ActiveSheet.ListObjects("Table1").Range.AutoFilter
End Sub

- not effect to those ... comboboxes (and those values)

 

[Ateeb Ali]

Okay Sit, thanks I amend it like this

Sub clearF()
    ActiveSheet.ListObjects("Table1").Range.AutoFilter
    ActiveSheet.ListObjects("Table1").Range.AutoFilter
End Sub

This was needed but now ignoring as seems to be no way
- not effect to those ... comboboxes (and those values)

 

[vletm]

Ateeb Ali
Why You do run that code twice.
... comboboxes
> as I wrote - I cannot or won't use those comboboxes ...
I could do this as I have give a sample for You too.
as well as
that Basic Filter works too.

 

[Ateeb Ali]

Dear Sir
our code just remove filter, i double it to re apply as well.