Filter without Auto Filter

[ExcelRoy]

Is there an easier way to use a column FILTER without using the very ugly AUTO FILTER ?

Thanks

 

[dave3009]

What do you mean. You could probably use VBA but it wouldn't be all that efficient in comparison to the built-in feature.

 

[ExcelRoy]

well, i have a spreadsheet that is like a million rows by 9 columns

is there a way i could enter a job number into cell and that shows all criteria for that job number?

kind a search button really, but presentable?

 

[schielrn]

You could set a command button to filter on a specific cells value. It would require using VBA code and you would still be using the built in autofilter feature. What cell would you want it to filter based off of?

 

[schielrn]

I am leaving work, but you can play around with this code, which is based off a value in A1 and you can attach this to a command button if you want:

Sub FilterA1()
    If Range("A1").Value <> "All" Then
        ActiveSheet.AutoFilter.Range.AutoFilter Field:=1, Criteria1:=Range("A1").Value
    Else
        ActiveSheet.AutoFilter.Range.AutoFilter Field:=1
    End If
End Sub

Hope that helps.

 

[ExcelRoy]

I have been reading (and getting confused) about using text boxes as search and search results

is this possible?

i have a link

http://www.mrexcel.com/forum/showthread.php?t=23469

what i would like is;

in textbox 1 is to enter search criteria, say "39555"

textbox 2 shows results

or something along those lines?

 

[schielrn]

I edited the code to be more dynamic, but it is still based off of a value in A1, you can change that to suit your needs.

 

[dave3009]

Well you would probably hide the big sheet and use a template with a text/combo box and search button. The action of the macro would perform the filter on-the-fly and return the results below your template header.

I used the following macro, you will need to recreate the bits n' bobs that make it run and adjust it but it does a nice quick job on my small dataset

Private Sub CommandButton1_Click()
Dim lst As Long
Dim crit As Variant
Dim clrows As Long
With Application
    .ScreenUpdating = False
        With Sheet1
            clrows = .Range("A" & Rows.Count).End(xlUp).Row
                .Range("A7:D" & clrows).Clear
            crit = .TextBox1.Value
        End With

        With Sheet2
            lst = .Range("A" & Rows.Count).End(xlUp).Row
                .Range("$A$1:$D$" & lst).AutoFilter Field:=1, Criteria1:=crit
                .Range("$A$1:$D$" & lst).SpecialCells(xlCellTypeVisible).Copy Sheet1.Range("A7")
                .Cells.AutoFilter
        End With
    Sheet1.TextBox1.Value = ""
    .ScreenUpdating = True
End With
End Sub

 

[ExcelRoy]

sorry dave, im not too good on vba, i know how to paste into a module etc, but how do i make my boxes etc, or do i run the macro?

is it possible to have text box 1 as the search box, text box 2 as the search results (however many)

thanks

 

[dave3009]

Sorry ExcelRoy

On sheet1 draw a commandbutton and a textbox from the controls tool box. Paste the code I provided into Sheet1 module.

Sheet2 holds your dataset. You said your set is 9 rows wide so each range in my code will need the D column reference to I to cover your range.

Let me know how you fair