Previous Topic Next Topic

Instant Auto Filter

Closed
Ronny - Feb 13, 2012 at 05:44 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Feb 20, 2012 at 09:14 AM
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,

Related:

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Feb 16, 2012 at 10:09 AM
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
0
Ronny
Feb 16, 2012 at 01:26 PM
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Feb 20, 2012 at 09:14 AM
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
0

Similar discussions

Any auto clicker for Xbox One?
vic11con -
julietdcosta -

13 responses