PDA

View Full Version : Autofilter custom shortcut



solsearcher
01-02-2008, 03:00 PM
I am using a spreadsheet that has 3 columns and about 2000 rows. I only filter column A1 (Products) and I use the autofilter > custom... 'begins with' and then I type part of my product code to find what I'm looking for.
This works well but is time consuming. I tried to create a macro to open the 'custom' dialouge box but it records the whole search which is no good for my problem.
I thought that I could create a button that acts as a shortcut to open the Autofilter Custom dialogue box but I have had no luck.
Would it be better for me to create my own form with a text field and search button?
Can somebody give me some advise please?

Bob Phillips
01-02-2008, 03:30 PM
Here is one way.

You put the begin string in a cell and the filetr code picks that up



Sub FilterData()
Const WS_CRITERIA_CELL As String = "D1" '<=== change to suit

With ActiveSheet

.Columns("F:F").AutoFilter Field:=1, _
Criteria1:="=" & .Range(WS_CRITERIA_CELL).Value & "*"
End With
End Sub

solsearcher
01-02-2008, 03:48 PM
Hi XLD,

Sorry I'm a new to this, can you break it down for me?

Bob Phillips
01-02-2008, 03:52 PM
in what way? How to install it, or what it does?

solsearcher
01-02-2008, 03:54 PM
Both?

Bob Phillips
01-02-2008, 04:07 PM
OK.

To install it, just add the code to a standard code module.

Then just add a button from the Forms toolbar, and assign that macro to it.

What the code does is to to add an Autofilter and to filter using a criteria of begins with the text that is in cell D1. The code assumes that the data to be filtered is in coolumn F, just change those two things to your situation.

lucas
01-02-2008, 04:13 PM
Hi Bob, John has a wink that shows how to get to the vbe. Once there poster can go to insert-module to insert a standard module.

http://xlvba.3.forumer.com/index.php?showtopic=361

This is for beginners..

solsearcher
01-02-2008, 05:19 PM
XLD, thanks a lot it works a treat. This is going to make my life much easier. Thanks again.

Jay