I want to provide to my colleagues at work advanced filtering in an extensive list with 5 columns and aprox 1000 lines. They are excel dummies, so I can't expect that they use the function properly. They should just insert their search criteria, without the need to think about the surroundings of excel.
For that I created the worksheet and defined a 3 row and 5 column wide area where the users can enter their search criteria. Now as I learned advanced search doesn't work if there are multiple criteria rows and in a column only the first entry is selected, and the other 2 search criterias are empty (they would need a value as well or at least a blank space). But only those cells that have a search value in the column. If there's no search criteria for a specific column, then all fields need to be empty in that column.
Beside that I need to add an asterik as pre- and suffix to any entered search criteria, otherwise data is not filtered (only looks for whole field content and not for parts of it).
I used below function for that, but it doesn't work out because it fills in empty spaces in all empty fields in the range, not only in the empty fields of a column with a search criteria ...
Any hints how I could resolve that dilemma of advanced search without having to define each range for itself?

the search criteria range is A2:E5 (including the required header with column names in row 2)
the data range is A7:E1000 (also including the required header with column names in row 7)



Sub AdvancedFilter()

Dim MyCriteria As Range
DIM MyData As Range

Set MyCriteria = Range("A2:E5")
Set MyData = Range("A7:E1000")

For Each Column In MyCritera
If Column.Value <> "" Then
Column.Value = "*" & Column.Value & "*"
ElseIf Column.Value = "" Then
Column.Value = " "
End If
Next

MyData.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
MyRange, Unique:=False

End Sub