VBA macro to filter rows based on text entered in query cell

Hello Excel friends!

Please look at the attached sample spreadsheet and explain to me why I cannot select (filter) only the rows that have a cell containing the partial data in the Query Box (C2) also know as "NameSearch".

E1:H16 is my raw data.
E20:H20 is my search criteria which appears once data is entered in C2, such as "reg". The criteria appears with asterisks on each side so partial data can be entered.

The problem is, no matter what I enter in C2, all data is transferred to RESULTS.

Thanks for any assistance!

Please Login or Register  to view this content.

Bonjour Marc L, merci beaucoup pour votre rιponse.
I'm not sure that you actually opened my attached spreadsheet and tried it, so I'd like to clarify for you or anyone else looking at this what we have here.
It is very likely that I'm confused by your response, so pardon me if so.
You referred to "=NameSearch" as a formula. Actually, and I probably shouldn't have done it, "NameSearch" is merely the name I gave to the cell "C2", because that's where I type in the partial text that I use to search through the names.
You stated that I "forgot to fill the criteria cells E20:H22" and I assume that to mean that is the (or one of many) reason(s) that it doesn't work. But actually, if you type any text in cell C2 you will see that it immediately fills in cells E20:H20), each surrounded by asterisks for obvious reasons. The CLEAR CELLS button is to clear the C2, CRITERIA and RESULTS cells to prepare for my next query.

So, given all that, I went in and deleted my three macros and replaced it with the one you so kindly created to accomplish my task.
Obviously yours is much shorter and more direct and obviously I'm pretty new at VBA, but I presently cannot get it to work as is. While I see you were able to create the script without using the Criteria cells I placed on the sheet, the result is the same as before. It still lists all the names in E2:H16 to the Results.
Is there any chance you might consider taking my spreadsheet and replacing my macros with yours and attaching it so that I (and others learning from this) can get it?
If we type in the text "hard", for example, the result should show five rows of names, each row containing all the names found in the rows that include the name RicHARD.
Also, since I can't quite figure out exactly what each line of your code is doing, will you please be gracious enough to put notes by each line explaining what it's doing?
Again, thank you so much for your time and effort.
Attachment 653830

It's what happens without a good enough explanation neither an expected result attachment …
And without opening your attachment I did not wrote any answer !

As my demonstration well works on my side for the result I imagine
without using the button but adding first the formula in E20:H20 …

A reminder for advanced filter criteria as written in its help :
on a same row that means 'AND' the reason why logically it can't work
except if each cell of a data row matches with the column criteria.
For an 'OR' each added criteria must be alone on a below row …

Your attachment is not valid ‼
Just follow the top page yellow banner …

I forgot to point out your initial error :

according to the advanced filter help, the criteria range must contains at least two lines,
the first for column header, the second for column criteria, but in your code you forgot the header row #19 …

Thanks Marc L. I followed your instructions concerning the horizontal AND and the vertical OR. I also corrected the error and included the header in the Criteria. It now works.
I would greatly prefer if the same thing could be done without using the credential cells like your first script.

Is that possible?

I am including the final result below including an image of my scripts.

Again, I appreciate your expert assistance.

Revamping your workbook in order to use only the NameSearch cell (if cleared all the data list is shown) :

• deleting the FilterMacros module
• so deleting the button ^^
• deleting the results range
  
  
• adding formulas in the criteria range
• inserting a new column in first column for moving the criteria range and hidding it
• namiing the criteria and the data list ranges
  
  
• updating the event procedure in the worksheet module :

PHP Code: 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target(1), [NameSearch]) Is Nothing Then
        If IsEmpty([NameSearch]) Then
            If Me.FilterMode Then Me.ShowAllData
        Else
            [NameSearch].Select
            [NamesCol].AdvancedFilter xlFilterInPlace, [NamesCrit]
        End If
    End If
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !

Marc L - I just want to say, your REVAMP is pretty incredible. I'm quite impressed and very appreciative.

Thanks (for both the appreciation and the rep') but I just apply an Excel Basics like any beginner can - must ! - operate …