Instant Auto Filter

[Closed]
Report
-
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
-
Hello,

I am using Excel 2010, and I would like to write some codes that does the following:
I have a data sheet of many entries, in rows 9~6000+
I have Autofilter enabled
I would like the event Macro that, when I type something in Cell D9, it would automatically be put into the filter in D10 and instantly look up what matches the cell in D9, with results showing from D11~D6000+
Sort of like Windows 7's start and search function.
I have been trying to write a code for this, with little success.

Thanks,

1 reply

Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Hi Ronny,

Try this code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D9")) Is Nothing Then Exit Sub
Range("D10").AutoFilter Field:=1, Criteria1:=Range("D9").Value
If Range("D9").Value = "" Then Range("D10").AutoFilter Field:=1
End Sub

When you clear the contents of D9, the autofilter will show all data again.

Best regards,
Trowa
Hey Trowa, Thanks for the trouble.

However, I am having trouble getting the event macro going - How do I get it going? I don't think the code is being used at all.

All I did was right-click on the sheet, view code, and paste it in the code area - But I don't think it is being used at all. Am I doing it wrong?

Thanks so much,
Ronny
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Hi Ronny,

As far as I can tell you implemented the code correctly.
The code will only be activated when a change is made in cell D9.
Besides that the autofilter must already be applied to cell D10.

To be complete as I can, you can find a download link below for the workbook I created to tackle your query.
http://speedy.sh/TEGMC/Ronny-IAF.xls
Just fill in the letter a, b or c in D9 for the autofilter in D10 to adjust and display the desired result below. Remove the contens of D9 for the autofilter to reset.

If you still get no action from Excel then check if macro's are enabled.

Best regards,
Trowa