Excel VBA Autofilter Compile Error?

Question

I am trying to create a search box that Autofilters as you type. I have used a variety of tutorials but I am struggling to get it working correctly.

The code I am using is:

Private Sub SearchField_Change()
If Len(SearchField.Value) = 0 Then
    Sheet1.AutoFilterMode = False
Else
    If Sheet1.AutoFilterMode = True Then
        Sheet1.AutoFilterMode = False
    End If
Sheet1.Range ("A:A" & Rows.Count) .AutoFilter _ Field:= 1, Criteria1:="*" & SearchField.Value & "*"
End If
End Sub

I am receiving a Syntax error when I try to use this, what I find confusing is that I am copying what is on tutorials (changing the sheet names/textbox names as appropriate) yet no success.

Subodh Tiwari sktneer · Accepted Answer · 2018-04-15 19:11:00Z

1

You need to specify the filter range correctly. The first cell in the range which is being filtered should be your header. So if the headers are in Row 2, try it like this...

Sheet1.Range("A2:A" & Rows.Count).AutoFilter Field:=1, Criteria1:="*" & SearchField.Value & "*"

answered Apr 15, 2018 at 19:11

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DisplayName · Accepted Answer · 2018-04-15 19:53:23Z

0

The flaw is in that:

Range("A:A" & Rows.Count)

Which would try referencing some "A:A1000000" range, which is obviuolsly an error

Then you want to use:

Range("A1:A" & Rows.Count)

Or, which is the same:

Range("A:A")

But I wouldn't reference the whole column and limit the range to the actually “used” one

So you may try this little refactoring of your code:

Private Sub SearchField_Change()
    With Sheet1
        .AutoFilterMode = False
        If Len(SearchField.Value) = 0 Then Exit Sub
        .Range("A1", .Cells(.Rows.Count,1).End(xlUp)).AutoFilter Field:= 1, Criteria1:="*" & SearchField.Value & "*"
    End With
End Sub

edited Apr 15, 2018 at 19:53

answered Apr 15, 2018 at 19:45

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

Collectives™ on Stack Overflow

Excel VBA Autofilter Compile Error?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related