Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

Filter Data As you Type

0

This should be a real easy one but it's so frustrating.

I have coded all columns and all are working...except Column M. It will not filter down the years for some reason?

This is so maddening to have it all work and then this issue, I've triple checked everything, what are your thoughts? (My only assumption is with the autofilter number at 13 is causing issues?) Also is there way to make the boxes look cleaner, hard to shape to fit.

Private Sub TextBox6_Change()

Dim filterInput As Range
Dim filterRange As Range

Set filterInput = Range("M2")
Set filterRange = Range("M4:M208")

filterRange.AutoFilter Field:=13, Criteria1:="*" & filterInput & "*", _
    VisibleDropDown:=False

End Sub

Answer
Discuss

Answers

0
Selected Answer

Hi Brad.

I think the problem is that column M values are numbers (but the "as you type" feature works on text).

In the revised file attached, I converted them to numbers by typing =""& into N5 then clicking on M5 which gives the table formula:

=""&[@Year]
Pressing Enter gives a complete column (with numbers left-justified like text). I then selected them, copied and used Paste Special (Values) into M5 (then deleted column N). Note that just formatting them as Text doesn't work.

Now if you type 201 into the filter cell for example, you'll see it auotfilters as you type to show rows for 2015- 2019 only.

Hope this fixes it for you (and you're not doing calculations from that column- if so, use the TEXT function).

Discuss

Discussion

Got this error today? What am I doing wrong?

"Can't find project or library"
Sroncey21 (rep: 64) Jan 13, '22 at 12:42 pm
Sorry Brad (I'm out and about) and it's hard to guess. If VBA fails and offers Debug, which line does it fail on? 
John_Ru (rep: 2857) Jan 13, '22 at 1:41 pm
See Microsoft guidance on Can't find project or library
John_Ru (rep: 2857) Jan 13, '22 at 3:43 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login