Autofilter custom shortcut

[solsearcher]

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]

Here is one way.

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

[vba]

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
[/vba]

[solsearcher]

Hi XLD,

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

[Bob Phillips]

in what way? How to install it, or what it does?

[solsearcher]

Both?

[Bob Phillips]

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]

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]

XLD, thanks a lot it works a treat. This is going to make my life much easier. Thanks again.

Jay