|
Filter Data to Show the Bottom 10 Percent of a Data Set in Excel - AutoFilter
This free Excel macro filters a data set to display the bottom 10 percent of the data set in Excel. This is a great macro for data analysis in Excel because it allows you to quickly and easily filter a large amount of data in Excel in order to view the most important data.
This macro is really easy to use and a great way to quickly filter your data. You only need to change the range in the macro to point to the first column of the table, data, or list which you would like to filter in Excel.
How Filter Macros Work
All of the elements below often appear within autofilter macros.
Range
This should be the start of the data set, table, or list which you would like to filter. This can also be the entire range reference to the table. For example, if the table was from cell A1:D450, you could put that as the range or you could put A1 as the range. You can do this because the autofilter feature in Excel will automatically try to determine the total range which you would like to filter.
Field
This is the number of the column within the data table that you would like to filter. The first field (1) is the very first column in the data set that will be filtered. This means that if your data starts in column B and your range is Range("B1") and you want to filter based on column D, you would put 3 in for the field.
Criteria
This is the criteria by which you would like to filter. Some of the macros have symbols within the quotation marks after this argument and those symbols (such as <,>,*,?, etc.) should be left where they are in order to retain the functionality of the macro.
Operator
You will not have to change this. This is simply the way to perform different types of filter features in Excel.
Where to install the macro: Module
Excel Macro to Filter Data to Show the Bottom 10 Percent of a Data Set in Excel - AutoFilter
Sub AutoFilter_Bottom_10_Percent()
Range("A1").AutoFilter Field:=1, Criteria1:="10", Operator:=xlBottom10Percent
End Sub
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
Why doesn't this work
Sub auto_close()
Dim Worksheet1 As Worksheet
Dim returnValue As AutoFilter
Set Worksheet1 = Worksheets("Prov Pmt Sched")
returnValue = Worksheet1.AutoFilter
End Sub
returns an Object variable or with block variable not set.
Ziggy
This topic on Ozgrid provides just what I need, but limited to only 2 criteria - my VBA is not up to extending the code to the ten criteria that I need. Can anyone point me in the right direction please?
VB:
Function AutoFilter_Criteria(Header As Range) As String Dim strCri1 As String, strCri2 As String Application.Volatile With Header.Parent.AutoFilter With .Filters(Header.Column - .Range.Column + 1) If Not .On Then Exit Function strCri1 = .Criteria1 If .Operator = xlAnd Then strCri2 = " AND " & .Criteria2 ElseIf .Operator = xlOr Then strCri2 = " OR " & .Criteria2 End If End With End With AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2 End Function
If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
I have a raw data table with lots of columns. I would like to be able to filter by some of the headings of these columns, without them actually appearing in the filtered table.
e.g.
Raw data:
Product Family Part Number Description Cost
Solenoid valves 321516 3/2 sol valve 25.5
Cylinders 231513 5mm stroke w/mounting 12.5
FRL 321355 Filter 4mm dia outlet 5
Cylinders 231513 10mm stroke w/mounting 15.5
I'd like to have a dropdown at the top to filter by Cylinders for example, without that column being in the resultant filtered table.
I would like to find a solution in Excel, as I don't have Access.
I've got a feeling i need to use query or something? Can someone point me in the right direction?
Thanks!
I've been using a particular spreadsheet d/b for several months and have just now begun having a problem with autofilter locking up. To get it working again, I remove the filter, then add it back. I use the autofilter quite a lot and it will lock up every 3-4 times I attempt to use one of the filtered columns. I am usingWindows XP Pro and Office 2003.
Thanks,
Ken
hi
can u supply code for Using Data-Filter-Autofilter on a Protected Sheet?
many thanks
Andy
hi,
Example:
A16: heading says X or Y? B16: heading says reference C = Media
A17 = Y B17 = Test001001 C17 = Media 1
A18 = Y B18 = Test001002 C18 = Media 2
A19 = Y B19 = Test001003 C19 = Media 1
A20 = Y B20 = Test001004 C20 = Media 1
Im using data-filter-autofilter on columns A => N
Q: what code do i need to copy append (or insert paste) which also resorts on column B that works on single and multiple selection..
"Single" selection...eg select Test001001 or Test001003 in column B, copy-paste (or insert paste) Test001001 or Test001003 and resort on B so that there are now 2 x Test001001 or Test001003 etc
"Multiple" selection...eg select Media 1 in Column C and copy paste (or insert paste) eg Media 1 and resort on B
many thanks
A
Hi Guys,
Sorry if this question has been asked/answered before but I couldn't
see it easily in a search.
I have a problem with autofilter in one particular worksheet, not all
of them.
In using autofilter via VBA I seem to get the last line of the
datatable irrespective of the criterion selected, i.e. in 613 rows of
dataI might get rows 34,45,67,98 which relate to my criterion but then
I get row 613 tagged on at the end. It happens for all my selections.
I've tried three different fixes and none worked
1) naming the exact range from row 5 (headers) column1 to row 613 col
22 as "dfilter1" and then using
Sheet12.Range("dfilter1").AutoFilter Field:=1, Criteria1:="Australia"
2) increasing the range dimensions to include rows beyond the last row
of data (i.e. lower row is now 615 not 613)
3) Including rows 5 to 65536 in my range name
I've also tried setting autoflter manually and still no joy.
I've never had this problem before - anyone else had it and sorted it?
Thanks
Peter
Hi all
I have an autofilter which filters the with dates between 2 dates. However, I want to add that the autofilter should also filter data with null date fields. Thank you.
My current codes:
Sub test()
Dim wsm As Worksheet
Dim fromDate As Date
Dim toDate As Date
Dim myWorkbook2 As Workbook
fromDate = "3/8/2010"
toDate = "6/8/2010"
ActiveSheet.UsedRange.AutoFilter Field:=1, Criteria1:= _
">=" & fromDate, Operator:=xlAnd, Criteria2:="
hi
In Col A I have entries in a list and am using Data-Filter-Autofilter to filter them. in columns B - E i have corresponding info
e.g
A4 = Heading
A5 = A
A6 = B
A7 = A
A8 = A
A9 = C
A10 = C
etc, etc
what is macro to copy filtered A's to Sheet1, B's to Sheet 2, C's to Sheet 3 etc (along side their corresponding info in B- E).
hope ive not confused
many thanks
Andy
|
|
