Currently I got 1 vba coding which only filter with 3 criteria. Can I alter the vba to have 4 to 5 criteria in the filtering? Below is my VBA coding. Let say I want to add 1 more filter for column C2...
Range("A2").Select
Selection.Sort key1:=Range("E2"), Order1:=xlAscending, key2:=Range("B2"), Order2:=xlAscending, key3:=Range("A2"), Order3:=xlDescending, Header:=xlYes, ordercustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
I found a great bit of Advanced Filter code that works great, and fixed a problem of clearing a cell breaking the filter.
But if I want to increase the criteria from 1 row to 2, so you can start to include And , Or operations, it breaks the filter. Even an attempt at a manual one fails, until you put the criteria range back down to one row, then it's fine again.
I've tried changing the Target Row to >2 but that didn't work. how to make the criteria range bigger, and no problems of breakage if you clear the cells? It makes for a very useful automated Advanced Filter.
Here's the code :
[Code] .....
Database = the named area of raw data.
DATA is the name of the raw data worksheet
The criteria range should be AZ1:BC3, but of course royally breaks it...
Is there a way to provide filter with a list of criteria but when it doesnt match all of the criteria it still uses the filter on the criteria that it does match?
E.g i have this code
ActiveSheet.Range("$A$7:$N$31997").AutoFilter Field:=1, Criteria1:=Array( _
"A", "B", "D", "E", "H", "I", "R"), Operator:=xlFilterValues
However sometimes for example B will be missing, or H or B H I will be missing etc... is there a way to provide all of the criteria and it will not error if the criteria is not all there?
I have a filter and need 2 (two) criteria.
1) In a (long) list of dates I need to see only the last month
2) From that selection (last month) I need to filter a date.
Note: I would like the filter to only show the last month as the list becomes very long.
Current solution
Column A has all the dates
Column B is a copy of column A
I use a small macro
I have a excel of about 5000 rows and 80 columns. Each row corresponds to a particular set of experiment. I want to reduce the number of rows by removing useless experimnets like controls etc.
I want to search the row for some key words(ex. control) and if it present then remove it. It should prompt user to enter the keyword and the original file should not be disturbed. It can be copied to 2nd worksheet.
In the advanced filter, I am given two criteria one is filter by unit price by less than 1000 and order no atleast 100 .
View 6 Replies View RelatedHow to give a cell link instead of any value for filter criteria, For example I need the data between two date range, the first date is in cell A1 and second date is in cell B1 by using macro. I have tried by using below code also but after filter all data hiding.
Dim a As Date
Dim b As Date
a = Range("a1")
b = Range("b1")
Rows("2:2").Select
Range("A2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">=a", _
Operator:=xlAnd, Criteria2:="
I have a Excel sheet which has a Product type values ASP,VSP etc in the first column. The second column has values Product Sub Types,which has values abc123, abc123456,abc123789, def123,def123456,def123789.The third column ServId's abc01,abc02,def01,def02.
If the Product Type is ASP,then I need values abc123,def123
But if Product Type is VSP, then I need values abc123
abc123456;abc123789
def123
def123456;def123789
I have a dataset, which I would like to sort on multiple criteria. The code I have is :
Dim Lst As Long
Dim Hdr As Range
Lst = Range("A65536").End(xlUp).Row
Set Hdr = Range("A6:AD" & Lst)
With Hdr
.AutoFilter
.AutoFilter Field:=9, Criteria1:="Complete" 'Match Status
.AutoFilter Field:=14, Criteria1:=Array ("Pending", "Technical", "PR", "Regional", "=") 'Case Study Status
End With
Field 9 Match Status will always be Criteria "Complete"
Field 14 may contain different variables depending on the dataset. I want the filter to bring back all Match Statuses (Field 9) "Complete" and multiple Case Study Statuses in Field 14.
This code filters correctly on Field 9, but is only bringing back blank cells in Field 9 when there is data that has Case Study Status "Pending", "Regional" and "Blank".
I would like to filter a row using the following criteria: date should be < or = todays date.
I have tried the below but it doesn't work
Selection.AutoFilter Field:=11, Criteria1:="
I am using the code below to coax Autofilter into letting me use three criteria. I can hard code the values in for two of the cells (B6 and C6 in this case) but for it to be of any use I need the array to use the actual values found in those two cells.
With Worksheets("Sheet2")
Cells.Autofilter field:=1, Criteria1:=Sheets("Sheet1").Range("B1").Value
Cells.Autofilter field:=6, Criteria1:=Array("2880", "2879"), Operator:=xlFilterValues, _
Operator:=xlOr, Criteria2:=Sheets("Sheet1").Range("D6").Value
End With
How should this be setup to allow the array to read the two actual cell values?
I have a table that I filter out each buyer
Buyer Item Weight
Smith PartA 1000
Smith PartB 1000
Jones PartA 1000
Jones PartB 1000
Fred PartC 900
So when I filter out Jones, it would look like this
Buyer Item Weight
Jones PartA 1000
Jones PartB 1000
With Jones cell reference A4 & A5. Can I link a cell to the 1st visible cell below the autofilter? This cell would change each time I selected a different buyer.
What i need to do is basically delete every row in my spreadsheet that contains the criteria "L-0.###" in said column. by ### i am referring to 3 decimals, such as:
L-0.250
L-0.000
L-0.555
and so on. you get the idea.
with the below program i got it working so i can type in:
L-0.***
and have it sort out everything proceeding L-0. ...problem is i need to keep the data such as this:
L-0.53
L-0.00
L-0.0
or basically everything not 3 decimals in with a head of L-0.
here is the macro i'm using:
Sub DeleteRowByString()
'
' DeleteRowByString Macro
' Macro recorded 7/10/2007 by Khenzel
'
' Keyboard Shortcut: Ctrl+Shift+S
'
'Get the criteria in the form of text or number.
vCriteria = Application.InputBox(Prompt:="Type in the criteria that should be removed from matching columns. " _
& "If the criteria is in a cell, point to the cell with your mouse pointer", _
Title:="Ferguson Enterprises, Inc.", Type:=1 + 2)
'Go no further if they Cancel.
If vCriteria = "False" Then Exit Sub
'Get the relative column number where the criteria should be found
lCol = Application.InputBox(Prompt:="Type in the relative number of the column where " _
& "the criteria can be found. (Ex. A=1, B=2, Etc.)", Title:="Ferguson Enterprises, Inc.", Type:=1)
if possibly a modification in my coding to make this work, or simply a criteria i can type in to make it happen.
Anyway, in my workbook I have a master sheet which contains all the information entered (this master sheet will continue to grow as more information as entered). I want to create a userform to allow someone to search the data by selecting a date range (from/to) and a criteria (Pending/Settled) and for the results to be copied into a new sheet (simply I don't want the raw information tampered with in an unregulated way).
View 3 Replies View Relatedi want to display the results of one criteria using VBA codes instead of just filtering one by one to display the result and collate it. I have attached the sample. I want to find the "subcodes" of name "kathy". to be able to do that, i filter "kathy" to display all the "codes" then filter all the "codes" to find the "subcodes". is there vba code for this one so that when i input the name, all the subcodes will be displayed?
View 6 Replies View RelatedI am trying to run a macro to filter data based on 3 criteria. i want to filter column c to show rows with "Application", "Application / Web", and "Application / Database" which is referenced in the criteria range stated below.... Sheets(" Validation").Range("D95:D97"). for some reason it does'nt pick up the answers when i know that they exist in column c. could it be that they are formulas populating column c?
Sub Macro3()
Range("c4:c200").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Validation").Range("D95:D97")
End Sub
I have been struggling hard to pull information out of a table using vlookup and match & Index functions. It does not seem to be working. Below is my criteria:
I have a data of call centre agents with names , dates and calls offered answered details. On another sheet i have given a list of names of agents in a drop down list and all the dates of the week. Now i want that when a user selects a particular agent name from the drop down list, the data fetched under '07042014' date column is against the number of calls answered by the selected agent on this date. I have 5 columns of title '07042014' , '08042014', '09042014', '10042014' and '11042014' Capture.PNG. Attach is the file how it looks like.
How to use match and index function or any other function to fetch information against this date as per the agent selected.
i have my "raw data" and in my "summary" sheet, i want to show the data by the data that is filter in range D15:D21
Essentially, when the user filters the data from cell D15, i want the the macro to paste the data row D27 down/across from the "raw data" table. in addition, i am not sure how to create dependent drop down.
for example, in cell D15 it shows "segment"; in the cell below, D16 (tyre size), i want it to list all tyre sizes within that selected "segment"; when i want D17, to show "speed index" for by segment and tyre size etc.
in the table, i want the macro to show the data based on what the user filters from above. i can then do my calculations but getting this to work is the tricky part.
Can I use vba advancedfilter to work with more than one criteria?
I presently have one range designated. At the top cell has the field, or column, header name being "Student", then followed by a list of 6 people, located in Sheets("Extract").Range("A1:A7"), which is then extracted from Sheets("Complete").Range("tblPrimary[#ALL]") to Sheets("Extract").Range("AA1") as in:
[Code] ......
I want to also be able to filter out a specific month, whose field/column name just happens to be "Month", but I suspect I will need to change it to "InfoMonth" or the like to avoid the probable key word of "Month"...
The months are numerical in those fields - 1 through 12.
Can I add to the present filtering line or do I need to then create an additional filter?
I have a range of data (up to 20,000 cells) that has up to 30 different entries. I need to apply a filter to this data to filter out all but 5 of these.
View 5 Replies View RelatedI have had no luck searching for an answer so better to ask. I am using auto filter on Sheet1 and filtering a column of dates. What I need is for the criteria for the auto filter to be pulled from cell H1 on Sheet 2. Is this possible or is auto filter the wrong direction to go? I am looking to find all the rows on Sheet 1 that contain the date found in cell H1 on Sheet 2. Then I am going to cut and paste that data to another worksheet. I think I have all the rest of it figured out but am stumped at the filtering portion.
View 9 Replies View RelatedI have a worksheet which has in column A2:A9 a label and in column
B2:B9 a value, like so:
A1="LABEL" B1="VALUE"
A2="LabelGreen" B2=452.47
A3="LabelBlue" B3=-87
A4="LabelRed" B4=9
A5="LabelGreen" B5=-7
A6="LabelBlue" B6=1888.97
A7="LabelRed" B7=144
A8="LabelGreen" B8=-0.02
A9="LabelBlue" B9=87002
I use the autofilter on column A to select only certain labels.
in cell D1 I use the subtotal formula to give me the sum of all
filtered values.
D1=SUBTOTAL(9,B2:B9)
This works fine, for example, if I filter on column A for "LabelGreen",
the formula in D1 calculates 452.47 - 7 - 0.02 = 445.45.
What I am trying to do now and what I have not yet found a solution for
is:
In cell C1 I want to display the criteria I have filtered for.
For example, when I filter column A for "LabelGreen", I want
"LabelGreen" to be displayed in cell C1.
I was thinking something similar to SUBTOTAL might do the trick, for
example:
C1=SUBFIRST(9,A2:A9)
But this only gives me (and I am not surprised) an error "#NAME?"
I'm trying to use an advanced filter to filter a large data set using several parameters. I had this worked out before but there have been some parameters added that have thrown my filter off balance and I'm struggling getting it back.
Here's the criteria I need to filter
Column 1:
85
Column 2:
3
Column 3:
BU 1
BU 2
BU 3
BU 4
Column 4:
BA 1
BA 2
BA 3
BA 4
BA 5
BA 6
BA 7
Since I have an uneven list of criteria, I'm struggling to figure out the layout with the 'AND' condition. I know I can copy the 85 and 3 figures to each line but when I have 4 figures in column 3 and 7 figures in column 4, how can I create the 'AND' condition for all these criteria?
I have a list of farmers in A1:A1000 with the types of livestock they keep in col B, delimited with commas and spaces e.g:
Col A Col B
Name Animals
Smith Cows, Pigs, Sheep, Horses
Jones Sheep, Pigs, Chickens, Geese, Alpaccas
Price Cows, Sheep, Pigs, Chickens, Rabbits
Williams Cows, Chickens, Horses, Alpaccas, Pigs
I need to be able to filter this list using up to 3 criteria, e.g. Filter all farmers with Cows, AND Sheep, AND Pigs. Applying this to the data above would show Smith and Price.
The user would need to enter the criteria somewhere, preferably in 3 cells, let's say D1, D2, & D3. I reckon I need to use Advanced Filter, but not sure how to do it with all the animal types to be filtered being in one column.
as u can see on picture I have some data and i want it to filter with "debet" and "credit",while debet can be zero or number x when in the same time credit can be zero or the same number x so when entering number x meaning e.g. 500 i get to rows
first
debet = 0 and credit = 500
second
debet = 500 and credit = 0
while running advanced filter i was getting nothing because i couldn't write what i needed
When doing advanced filter in VBA, is there a way to set the criteriarange, without having actual cells on a worksheet with the criteria in?
I've tried criteriarange:=Array("Currency", ws.Name), but it didn't like it.
i have rec data in which I need a macro that will filter for CASH NOMS and keep everything thats in the criteria but delete everthing else in cash Noms
criteria is named range "Criteria" (I5:K10)
So from this
Sheet1 ABCDEFGHIJKL4 Criteria 5RecTeamAmountRef RecTeamRef 6CASH NOMSAMP1132.36CAETRN CASH NOMS*AMP*.AIMS 7CASH NOMSAMP215.36.AIMS546.3 *WTRN96 8CASH NOMSINTEREST915.12WTRN96 *AMPTRH 9CASH NOMSFA5-65.36AMPTRH *.WSTFGH 10CASH NOMSFA6-9.01CBWSSG *WTWHAT 11CASH NOMSCA-9.01CBWSSG 12CASH NOMSDES-9.01MSFAST 13CASH LIFEUUTU-9.01CBWSSG 14CASH LIFEAMP1-9.01CBWSSG 15CASH LIFEAMP232.12.WSTFGH 16CASH LIFEAMP315975.36CBWSSG 17CASH LIFEAMP4-9.01OPTMOD 18CASH NOMSFA1-9.01CASH WTWHAT Excel tables to the web >> Excel Jeanie HTML 4 .........................
I've created a Macro in Excel that takes the value from the selected cell and filters an Access DB for that appropriate record.
I'd like to modify the button so that if the cell does not contain content that begins with "DR-" the button is greyed out or inactive so that it cannot be selected until a cell with the proper content is selected.
I currently use this filter to display rows with exact values as shown in this example:
Sub New_SheetUnpaidInvoices()
Application.DisplayAlerts = True
Worksheets("Closed Yr. 07").Activate
Columns("S:S").Select
Selection.AutoFilter
ActiveSheet. Range("$S$1:$S$1127").AutoFilter Field:=1, Criteria1:= Array( _
"BOB JONES", "JIMMY SMITH", "SUSAN LEE", "JONES SMITH LEE"), Operator:=xlFilterValues
End Sub
Sometimes, however, the worksheet cells contain simply combinations of last names like "JONES SMITH LEE" or other combinations. And, the last names appear in random order. So, I'd like to able to filter all rows that contain portions of the string without having to worry about the order and the exact string, etc. If the autofilter can return rows anytime it finds Jones, Smith or Lee regardless of order, that's be great.
I have mutliple non-contiguous columns that are SUPPOSED to contain dates. If the particular item for that coulmn doesn't require a date then "waived" is typed; others may be blank. I want to apply a filter that will tell me if any one of the columns YEAR is older than "2004". I'm really bad at formulas to bare with me.
I inserted the following formula: =IF(OR(YEAR(N6)<=2004,YEAR(V6)<=2004,YEAR(X6)<=2004,YEAR(AB6)<=2004,YEAR(AD6)<=2004,YEAR(AF6)<=2004,YEAR(AI6)<=2004),"EXPIRED","GOOD")
The problem is that if the cell is blank or if contains "waived" it either produces "#value!" or it reports "True" when it is actually "False". I'm not sharp enough to know how to account for those two conditions in my formula.
