|
Filter Data in Excel to Display Records that Contain a Value Between Two Values - AutoFilter.
This free Excel macro filters data to display only those records or rows that contain a value between two values. For example, you could display all of the salesmen whose sales are between $50,000 and $75,000 for the quarter. You can use any number and apply it to any data set that contains number where you need to display all of the records that are within a certain range.
To use this macro, just replace "50000" with the number you want to use for the lower end of the range and replace "75000" with the number that you would like to use for the upper end of the range. Make sure to leave the ">" and "<" in place because this is what makes the macro work.
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 in Excel to Display Records that Contain a Value Between Two Values - AutoFilter.
Sub AutoFilter_in_Excel_Above_Below_Num()
'This autofilter macro displays records with a number between 50000 and 75000 within the first field - in this case in column A
'It is important to retain the ">" and "<" signs within this macro because that it what makes this work correctly
Range("A1").AutoFilter Field:=1, Criteria1:=">50000", Operator:=xlAnd, Criteria2:="<75000"
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
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
I am trying to create a form that will help engineers know what they need to do. Basically, create an open item list on a subform for all records that have a NULL value in ProblemDescription in the QREVALUE Table.
My Main Form is called QRE VALUE.
I have just 11 fields in the table and I don't need to display all of them.
I just want to display these fields 3.
TagNumber
QPRQPINumber
ProblemDescription
I would like to select the field in the subform and have it highlight the record in the main form. ( So that the user can enter the information if they chose or see all the fields and their content)
I would also like to have a listbox to make a selection that would allow me to filter the data. Basically something with 4 Names on to chose from. This could be a tabbed menu or listbox or what ever. (This is so that each user could see "their" open items rather than sorting through others.
Is there a way to customize a filter, similar to auto filter, but allowing a
user to enter their search criteria in a cell at the top of the worksheet.
Example: In Cell B2, enter modular and have the worksheet then
filtered showing only those records with modular within the record.
This is being used by a number of sales associates who lookup items they
need to price for proposals. The worksheet contains 14,000 + records, with
column headings of Mfg, Part Number, Description, U/M, Cost, Price A, Price B
and Price C.
--
Respectfully,
Chris H
Seattle, WA
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
Feast or famine with Excel issues
I guess this is my week before the Holiday
I can view the data how I need to by conditional formatting and pivot tables but HR wants to get rid of all rows that:
only have 1 accounting line per document such as Doc MI310712 in attached sample
So I only want to display records if there is more than 1 accounting line for each document
My understanding of Excel is that in order to get rid of rows that a VBA macro is needed since there is no function to do it
My VBA is limited but is there a way to specify criteria in a filter to hide the rows using a formula?
Many thanks
Excel 2007 compatability mode for 2003- I have a list tab with text values in column C "ID" and a tab with a pivot table. The pivot table had 500,000 rows of underlying data. Based on the cell values of "ID" in column C(in each row), I would like to be able to see the underlying data of the pivot table for only the rows of underlying data where the uderlying data field "ID" matches the "ID" value in column C.
Note that the pivot table can not use the field "ID" becuase there are too many values and it can not represent those.
Also note that I need to be backward compatable to 2003.
I tried a hyperlink but am not sure how to reference the underlying data or how to programatically spawn the results.
If I have Jan, Feb, Mar,............Nov, Dec fields in a query and would like to use the filter criteria to not show any record that is zero for all 12 months, what do I type?
I tried using 0 in the YTD sum field but some records I don't want removed might be 25 in january then -25 in february, thus adding up to 0 for the year.
Thread closed...solution found
Hi,
Hopefully someone can see the error of my ways here.
The code, although still a bit messy, works as required except when there is only one record to be copied. When only one record to be copied it copies the record 65536 times to the new sheet.
Any pointers much appreciated
VB:
Sub CopyAllTodayRecords()
Dim WhichWS As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim DateFrom As Long
Dim DateTo As Long
DateFrom = ActiveWorkbook.Sheets("TotalsPage").Range("B2").Value
DateTo = ActiveWorkbook.Sheets("TotalsPage").Range("B3").Value
'Clear any previous auto filters
Call ShowAllRecords
Set WhichWS = ActiveWorkbook.Worksheets("Database")
With WhichWS
'WhichWS.UsedRange.AutoFilter Field:=3, Criteria1:=DateFrom
WhichWS.UsedRange.AutoFilter Field:=2, Criteria1:=">=" & DateFrom, Operator:=xlAnd, Criteria2:="<=" & DateTo
End With
'Hide the columns we dont want to copy
ActiveWorkbook.Sheets("Database").Columns("B:D").EntireColumn.Hidden = True
ActiveWorkbook.Sheets("Database").Columns("O:O").EntireColumn.Hidden = True
With WhichWS.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error Goto 0
End With
If rng2 Is Nothing Then
Worksheets("ObservationInfo").Range("A8:N65536").Cells.ClearContents
MsgBox "There are no entries for the date you have selected"
Else
Worksheets("ObservationInfo").Range("A8:N65536").Cells.ClearContents
Set rng = WhichWS.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("ObservationInfo").Range("A8:N65536")
End If
'Show all columns on Database sheet
ActiveWorkbook.Sheets("Database").Columns("A:S").EntireColumn.Hidden = False
'Clear auto filters
Call ShowAllRecords
End Sub
If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
Hi,
I have a query with Pivot Tables. When I apply an autofilter on a Pivot Table it doesn't show the columns with all zero values, how do I get the table to show these columns?
My motivation for showing these columns with zero values is that I need to calculate some metrics (eg. top left cell of one pivot table divided by top left cell of another pivot table, in %) and so need the pivot tables to be of the same dimension.
Any help on this matter would be much appreciated.
Cheers
Is there any way to tell excel that I want the numbers (i.e, records) across the top of the screen and the letters (i.e, fields) down the side?
A B C D E F...
1
2
3
4
5. . .
Thanks.
|
|
