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

Filter Data as you type - TABLE

0

I can not get my data to filter when I am trying to set up a filter as you type kind of filter. I saw a video of the TeachExcell but in the video it was a range not a table. please Help. 

Table Name: Append4

Tab:ALL

This is how far I have done it. 



Private Sub TextBox1_Change()



Dim filterInput As Range

Dim Append4 As ListObject



Set MainSheet = Sheets("ALL")

Set Append4 = MainSheet.ListObjects("Append4")

Set filterInput = Range("C15")



MainSheet.AutoFilter Field:=3, Criteria1:="*" & filterInput & "*", VisibleDropDown:=Flase







End Sub
Answer
Discuss

Discussion

Your question isn't clear. Please modify it to explain what you want Your point of entry would be to say what your code does and what you want it to do instead.
Variatus (rep: 4889) Jun 3, '21 at 7:41 pm
The question is self explanatory. if you dont know what you are looking at watch this video. https://www.youtube.com/watch?v=osGqAZ2BquA I am trying to create a search cel that filters dates as you type. 
inasir Jun 4, '21 at 8:14 am
Add to Discussion

Answers

0

Inasir

The "filter as you type" macro from Don's video tutorial Filter Data as you Type in Excel DOES work with a table.

You didn't attach a file (or a tutorial link, like I did above) but this modified code worked for me, given the case you provide. Note that the three key changes as in bold below.

Private Sub TextBox1_Change()

Dim filterInput As Range

Dim Append4 As Range

Set Mainsheet = Sheets("ALL")
Set Append4 = Mainsheet.Range("Append4")

Set filterInput = Range("C15")


Append4.AutoFilter Field:=3, Criteria1:="*" & filterInput & "*", VisibleDropDown:=Flase

End Sub
Hope this works for you (and thanks for asking a question on that interesting tutorial- I hadn't seen it).

REVISION 1: File added to help another user.

Discuss

Discussion

Just spotted your question refers to a "...search cell that filters dates as you type". The code above doesn't do that, it filters strings.

Given Excel stores dates as numbers (e.g. 44353 represtent 06 June 2021) which can be enetered and displayed in many formats, filtering dates will be much harder I think. You need to give us a workbook and say what kind of entry you expect users to do (and what filter you get e.g. does  07 mean filter the day, the month or the year or all three?)
John_Ru (rep: 6102) Jun 6, '21 at 10:32 am
Inasir

What is your response please?
John_Ru (rep: 6102) Jun 7, '21 at 8:46 am
Add to Discussion
0

Simply don't really work. RThe data on the table completely disappears. And can't find a way to bring them back

Discuss

Discussion

Hi.

Bit surprised you'd use this Answer (from 14 months ago)- it needs to have a named sheet and a named range to match the code. I've now attached the file I talked about to the Answer above but the user never came back.

Suggest you go back to to Don's tutorial and work from there.

Regarding your file, if the table has headings and the filter down arrows show ( but no data), hoghlight the header row and go (ribbon) Home/ Sort& Filter then click the Filter button. The table filters should then be reset.
John_Ru (rep: 6102) Aug 19, '22 at 9:23 am
Add to Discussion


Answer the Question

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