Help: Advance Filter

I want to make the file more automatic and user friendly. All the data set as table and it will auto run if there is any additional information.
Refer to sheet [Search], maximum 5 criteria is allowed to search the data.
Problem is all database will be shown if row 6 is not in use and left it blank. How to tell program blank mean not in use?

Please refer to file attached.

Hello
If you leave the last row blank it will return All records. You could just not have the 5th. line until you need it, it is a dynamic table and the criteria will be updated to include the new line. Alternatively, with your Data Validation drop down lists select 'All' in one or more fields and the filter will ignore that line because there's no match in the Database. See the amended file I've attached. I'm using Macro2 on the 'Click Me' command button.

Hope this helps.
DBY

Originally Posted by DBY

Hello
If you leave the last row blank it will return All records. You could just not have the 5th. line until you need it, it is a dynamic table and the criteria will be updated to include the new line. Alternatively, with your Data Validation drop down lists select 'All' in one or more fields and the filter will ignore that line because there's no match in the Database. See the amended file I've attached. I'm using Macro2 on the 'Click Me' command button.

Hope this helps.
DBY

Refer to Macro 2, this code was removed. Without this code, sheets "show" will not deleted the previous record.

 Sheets("Show").Select
    Cells.Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select

"All" = it can be one of the solution. It is possible make to write in macro that blank is to be ignore?

Thanks.

Hello
Try changing the Macro2 code to:

Dim c As Range
Dim r As Integer

For Each c In Range("SearchCriteria[#Data]")
    If c <> "" Then
    r = c.Row
End If

Next c

Range("Data").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Search").Range("A1:D" & r) _
, CopyToRange:=Sheets("Show").Range("A1:G1"), Unique:=False
        
Sheets("Show").Select

Refer to Macro 2, this code was removed. Without this code, sheets "show" will not deleted the previous record.

Not sure why you would use this as whenever the Advanced filter is run it automatically removes the previous filter results.

DBY

Glad all's working.

How is your code work to auto remove previous filter results? Which code is refer to this?

I didn't add any code for this. It is part of an in built feature of the Advanced Filter. The 'CopyToRange' is always cleared when run.

DBY