Filter without Auto Filter

ExcelRoy

Well-known Member
Joined
Oct 2, 2006
Messages
2,456
Office Version
  1. 2007
Platform
  1. Windows
Is there an easier way to use a column FILTER without using the very ugly AUTO FILTER ?

Thanks
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
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-known Member
Joined
Oct 2, 2006
Messages
2,456
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
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

Well-known Member
Joined
Apr 4, 2007
Messages
6,939

ADVERTISEMENT

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:

Code:
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.
 
Last edited:

ExcelRoy

Well-known Member
Joined
Oct 2, 2006
Messages
2,456
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Apr 4, 2007
Messages
6,939

ADVERTISEMENT

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-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
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

Code:
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

Well-known Member
Joined
Oct 2, 2006
Messages
2,456
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
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 ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,126,993
Messages
5,622,041
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top