keep header row in advanced filter

[ajm]

Folks, repurposing an old macro and need some help. The macro filters a range of data based upon the value searched for by the user. The search term is typed into A2. B2 contains a formula to add wildcards to the value at A2. ="*"&A2&"*" so if I am looking for "life" in the range, the criteria to search becomes *life* and the results listed would be life, proliferate, lifesavings, lifeguard, midlife, lowlife.... etc


The filter macro, which picks up the search term at B2 (named range "Criteria") is simple:

Sub Filter()
'///filter list in place using A2 as criteria

Dim s As Worksheet

On Error Resume Next

Set s = Worksheets("Finder")
s.Activate
With ActiveSheet
.Unprotect ("word")
.ShowAllData


Range("a4").CurrentRegion.AdvancedFilter _
    Action:=xlFilterInPlace, _
    CriteriaRange:=Range("Criteria")

.Protect ("word")
End With

End Sub

How do i keep the header row (row 4) visible and also display the filtered data set?

 

[kevin9999]

Do you have anything in row 3 that would be throwing your .CurrentRegion out?

This code:

Sub AdvancedFilter()
    Dim Criteria As Range, RngData As Range
    
    With Worksheets("Finder")
        .Unprotect "word"
        Set Criteria = .Range("B1:B2")
        Set RngData = .Range("A4").CurrentRegion
        RngData.AdvancedFilter xlFilterInPlace, Criteria
        .Protect "word"
    End With

End Sub

Turns this:

test.xlsm
	A	B
1		Header1
2	life	*life*
3
4	Header1	Header2
5	life	data
6	life savings	data
7	proliferate	data
8	data	data
9	data	data
10	data	data
11	life	data
12	life savings	data
13	proliferate	data
14	data	data
15
Finder

Cell Formulas
Range	Formula
B2	B2	="*"&A2&"*"

Into this:

test.xlsm
	A	B
1		Header1
2	life	*life*
3
4	Header1	Header2
5	life	data
6	life savings	data
7	proliferate	data
11	life	data
12	life savings	data
13	proliferate	data
15
Finder

Is that what you want?

 

[ajm]

Do you have anything in row 3 that would be throwing your .CurrentRegion out?

This code:

Sub AdvancedFilter()
    Dim Criteria As Range, RngData As Range
   
    With Worksheets("Finder")
        .Unprotect "word"
        Set Criteria = .Range("B1:B2")
        Set RngData = .Range("A4").CurrentRegion
        RngData.AdvancedFilter xlFilterInPlace, Criteria
        .Protect "word"
    End With

End Sub

Turns this:

test.xlsm
	A	B
1		Header1
2	life	*life*
3
4	Header1	Header2
5	life	data
6	life savings	data
7	proliferate	data
8	data	data
9	data	data
10	data	data
11	life	data
12	life savings	data
13	proliferate	data
14	data	data
15
Finder

Cell Formulas
Range	Formula
B2	B2	="*"&A2&"*"

Into this:

test.xlsm
	A	B
1		Header1
2	life	*life*
3
4	Header1	Header2
5	life	data
6	life savings	data
7	proliferate	data
11	life	data
12	life savings	data
13	proliferate	data
15
Finder

Is that what you want?

that works for me. Thanks Kevin.