Excel VBA Course
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

Dynamic Filtering - Something Odd Occurring. Not showing all rows.

0

Hi there. This is my first forum entry. I found the forum thanks to the YouTube videos I've been watching.

I have a spreadsheet that I would like to be able to dynamically filter without duplicating to another sheet or creating a table (the content grows so the range will grow too).

Anyway, what's happening is:

If I filter on the column for, for example 306, there's about 46 entries but if I use the dynamic filter I get about 4 entries.

Private Sub JobNumber_Change()

If Len(JobNumber.Value) = 0 Then

ActiveSheet.AutoFilterMode = False

Else

If ActiveSheet.AutoFilterMode = True Then

ActiveSheet.AutoFilterMode = False

End If

ActiveSheet.Range("A2:L" & Rows.Count).AutoFilter field:=4, Criteria1:="*" & JobNumber.Value & "*"

End If

End Sub

Excel usually either works or throws up an error so this one is weird.

Thank you to anyone who may be able to shed light on this oddity.

Martin

excel
MS Office Version: 365
posted by: martinkoss on Thu Nov 11, 2021 at 2:30 pm
Answer
Discuss

Answers

0

Martin

It is odd and I'm not sure how to fix it (or have time at present) but here's a pointer (and a revised file to illustrate it)...

I think it has something to do with filtering plain numbers and mixed alphanumerics (strings) using a string. In fact the same happens if you type 306 into the a "Contains" search in the column autofilter.

What I mean is that if you prefix every cell in column D with a single quote mark ' say (making it like a string to Excel), then your existing macro will return 40 or so results for a search of "306"- the attached file is in that state as it leaves me. It doesn't work is a (less obvious) space is used to prefix the contents however.

Not sure how this helps but good luck.

posted by: John_Ru (rep: 6142) on Fri Nov 12, 2021 at 7:54 am
Discuss


Answer the Question

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