Multiple AutoFilter Criteria Code

Feb 27, 2007

I'm trying to program a macro that filters out codes in an autofilter list. There are about 40 codes in total, however I only want excel to display 3. The current script I am using is below. I know excel lets you filter for 2 criterias in this format, however is 3 or more too much? What would be the best way around this problem?

Selection.AutoFilter Field:=4, Criteria1:="=COR", Operator:=xlOr, _
Criteria2:="=REM", Operator:=x1Or, _ Criteria3:="=REA"

View 2 Replies


ADVERTISEMENT

Looping Code & AutoFilter Criteria

Aug 12, 2006

I'm looking to use Autofilter and current region to select each persons data and paste to a new sheet.

The trouble is i'm not sure how to change the criteria name in the loop

e.g first name in list is Jack, next is John


Selection.AutoFilter Field:=1, Criteria1:="Jack"

View 9 Replies View Related

Shows Error In Autofilter Code If Criteria Not Match

Jun 13, 2013

VB:

Range("a1:n" & row_end).AutoFilter 13, "ABC"

If the value "ABC" not found in table, it shows error msg, In my file data="ABC" avail, sometimes not.

View 2 Replies View Related

Autofilter With Multiple Criteria?

Oct 5, 2012

I have some data which I want to apply multiple criteria to for a particular column. Searched around on the internet and it would appear I should use an array and pass that to my criteria. What I can't find an answer for is how to say "does not equal any of the values within the array"

Code currently is:

Code:
Dim NumberFilter_Array(0 to 2)
NumberFilter_Array(0) = 2
NumberFilter_Array(1) = 9

[Code].....

I've tried variations for that in red but can't get it to work, how to correct the line in red to filter for none of the values in the array NumberFilter_Array?

View 2 Replies View Related

AutoFilter With Multiple Criteria

Nov 1, 2012

I am trying to use VBA to set an autofilter that hides all zeros in Column AL and then excludes all values in Column E that start with "312" or "502". For some reason I can't get this to work as expected, it still continues to display unwanted values in Column E.

HTML Code:
ActiveSheet.Range("$A$1:$CS" & LastRow).AutoFilter Field:=5, Criteria1:=Array("312*", "502*")
ActiveSheet.Range("$A$1:$CS" & LastRow).AutoFilter Field:=38, Criteria1:=Array("0")

View 5 Replies View Related

Multiple Criteria On Autofilter From Single Cell

Mar 18, 2013

I'm trying to add to my code an autofiler with multiple criteria, the criteria is held in single cell. The criteria is made up of anything from 5 to 30 cells, then i have tried to concatenate these separated by commas, in speech marks, speech marks and commas etc., set them as an array the use that in my filter, but nothing seems to work.

View 6 Replies View Related

Using Autofilter And Multiple Criteria - Array Is Not Working Properly

Oct 12, 2012

I thought I had been able to use array to have more than two criterias with Autofilter, but now I am unable to make the following code work. What I am trying to do is have all the records that does not contain either N/A, S/O or xx and also have a filter on column 125 for the value "OUI".

VB:
Sub test()
Set ws1 = ThisWorkbook.Sheets("SOMMAIRE_EN_ALL")
Set ws2 = Workbooks("Fichier_central_2013_anglais_2_CLEAN").Sheets("DETAIL_CONCAT")
Set r = ws2.Range("A1:du4783")

[Code] .....

View 3 Replies View Related

AutoFilter Multiple Field Automatically (and Show All If Criteria Is Blank)

Jun 11, 2014

I am looking to adapt a piece of code (originally created by Ger Plante) so that it autofilters multiple columns of a table. I have three data validated lists that need to search 3 different columns in the table and filter accordingly, but also show all if no hits are made (hence why Ger Plante's code) was perfect in most respects. I would ideally like to keep the code as a Worksheet_Change event, but can deal with it being run as a normal Macro via a button if this is necessary.

[URL]....

VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1")) Is Nothing Then
Range("A5:C5").AutoFilter Field:=1, Criteria1:=IIf(Trim(Range("B1").Text) = "", "<>", "=") & Range("B1").Text
End If
End Sub

View 3 Replies View Related

Autofilter Macro: Goto The Next Criteria Down Until Certain Criteria Is Reached

Oct 22, 2008

I am trying to make an "intelligent" auto filter that with filter with increasing restriction until a certain criteria is met.

The list runs from A5:G20. In coloumn G is the number of hours associated with each event. And in A1 I have the percentage of items showing/whole list so it I have 15 rows on the list, and I filter so that only 5 are showing, cell a1=33%

How can I make a macro that will autofilter until the the a1=5%
Like having filter criter = equal or greater then 1 hour,
if a1 > 5%
Then criteria + 1 hour
If A1=<5%, then stop.

basically a seed criteria of 1 hour, adding 1 hour until the value in a1 = 5%

View 6 Replies View Related

Multiple Criteria Row Deletion Code

Aug 14, 2007

modify the delete row macro below. Unfortantely this is where my brain cell sometimes dies and the easy answers are as apparent as they should be...

Ideally what I am looking to do is have a growing list of "IDS" in one worksheet (call it delete IDS) that will be used to loop thru the sheet named "NRs" and delete the the IDS from that worksheet.

Sub DeleteNames()
'using set column
Sheets("NRs").Activate
Dim RngCol As Range
Dim i As Range
Set RngCol = Range("A:A")
For Each i In RngCol
If i.Value = 156916233 Or i.Value = 600093 _
Or i.Value = 156970571 _
Or i.Value = 156948409 Or i.Value = 30560 _
Or i.Value = 163841503 Then _
i.EntireRow.Delete
Next i
End Sub

View 4 Replies View Related

Unique Trace Code (multiple Column Criteria)

Aug 20, 2013

I have a challenge I haven't quite been able to figure out:

I need a 6 digit trace code to be automatically generated for each job based on the date, the product type and the job number.

The first 2 digits on the trace-code are alphanumeric and the sequence for each job is AA, AB, AC etc.

The last 4 digits are a date code (YYWW.... two digits for the year and two digits for the week number).

The criteria is that multiple jobs of the same product type within the same work week must not have identical trace codes. So Job 1 gets "AA" plus the date code... Job 2 gets "AB" plus the date code, etc. If Job 3 is done in the next work week, it cycles back to "AA" and gets the new date-code.

Generating the date-code was simple enough, but coming up with a function or rule to assign the two-digit alpha has been a challenge that I have not been able to beat.

Below is an example of how the Trace-code column should work....note that Trace code on one product type might be identical to the trace code on another product type.

A
B
C
D

1
Date
Product type
Lot number
Trace code

[Code] .........

View 3 Replies View Related

Macro Code: Find Cell Or Row Multiple Criteria

Sep 14, 2006

I can't seem to find the answer to this. In VBA, how do you Find a cell or row based on multiple criteria? All I need is to find it's location. If it helps, here's an example:

Sheet 1 contains source data:
Date Name Time Complete

Sheet 2 contains an interactive worksheet using this data. On Sheet 1 there's an entry: 9/12/2006 George 03:30 PM

Sheet 2 displays those records with adjacent cells for entering data. When data is entered, I need the system to go back to Sheet 1, find the entry that matches this one, and put an "x" in the Complete column.

View 6 Replies View Related

Autofilter On More Than 2 Criteria

May 15, 2008

Say you have a long list of data, and you go to Data menu --> Filter --> AutoFilter

And then you want to use the Custom AutoFilter. Here's a screenshot:

[url]

Is there any way to do MORE than two autofilters?

View 12 Replies View Related

MSG Box Autofilter Criteria

Jul 23, 2009

Been looking at various options. Just want something to simply show the autofilter critera (1 & 2 or how every many there are) in a text box. The range being filtered is column A : M.

Basically taking this further, want to use the criteria of column C to lookup an email address e.g.

'C' is filtered by "Company 96"

Lookup "Company 96" from sheet2 range A:B, with column A containing name, and column B containing email address

View 9 Replies View Related

Macro - AutoFilter And Criteria

Aug 7, 2014

My problem is following: I have a list of data that are classified according to a particular character, and I want to copy (with auto filter through the macro) the relevant information to the appropriate place in the sheet where it belongs. That's no problem. The problem is that if I want to copy data, classified by a character that is not listed in the table (that is not in the filtering criteria), then all the data are copied to the appropriate place. But I do not want to copy in this case nothing. How should such a macro look like?

Find attached an example : example.xlsm‎

View 3 Replies View Related

Autofilter With Offset Criteria

Dec 4, 2011

This is the following code i have a problem with:

ActiveSheet.Range("$B:$J").AutoFilter Field:=1, Criteria1:=Sheets("Report").Cell(0, -2).Value

I am trying to filter items on a sheet called "all the answers" and i want to look up a value on the sheet called "Report" to filter.

The cell i want to filter is two cells to the left of the active cell on that sheet.

View 5 Replies View Related

Adding Criteria To AutoFilter?

Apr 30, 2014

Is there a way to add a dynamic number of criteria(based on user input) to an autofilter.

Code:
ActiveSheet.Cells(1, 1).AutoFilter Field:=1, Criteria1:= dynamicfilters

View 4 Replies View Related

Use Cell As Autofilter Criteria

Aug 18, 2008

I have one master sheet of data, with a large number of fields and data.

I need to turn this master data into individual records, each record exisiting as an individual worksheet - lets call it a 'U'. The U is a template sheet which has calculations and lookups built into it to complete further information. The completed U's are then used by a number of people for different reasons. There are 3 main 'flavours' of these sheets which have slightly different uses.

I've gone from knowing nothing about macros to having learned enough about them in the last week or so to populate each individual sheet with the data, and save the new file in the location I want it to go.

What I want to do now is filter the fields displayed by the individual U sheets, as not every field is applicable to each 'flavour'. I've marked up the rows as to the appropriate flavour - e.g. Row 17 is applicable to 'P' 'F' and 'R' ( Cell which is auto filtered contains PFR), but Row 18 is only 'P' and 'F' (Cell contains PF).

I've gone through the master file and identified each entry as a the appropriate flavour - to summarise what I'd like to do now:

1) Automatically populate the template file with the relevant data. (which my macro will do)
2) Use an autofilter to filter the rows equal to the data in the reference sheet so these are the only ones displayed. Eg. Reference sheet says 'P', so I want to filter the U sheet where autofilter column contains the letter 'P'
3) Rename the file and save as my reference in the location I want it to (which the macro is doing).

Here's what I've got:

Range("CF3").Select
Selection.Copy
Windows("USS iss1.xls").Activate
Range("G158").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.AutoFilter Field:=1, Criteria1:=ActiveCell, Operator:=xlAnd

(where CF3 = the cell in the master data with the flavour in it, "USS iss1" is the template U file, G158 is a spare cell and Autofilter Field 1 contains the row reference which tells me which data applies to which flavours).

I've tried using the macro recorder, which when I paste the value in the autofilter/contains box records it as the value I've just put in rather than a copy of the reference cell. I've tried

Criteria1:=*ActiveCell*

and other variations on the same theme, but to no avail - I get "Compile error: Expected:expression".

View 3 Replies View Related

Show Autofilter Criteria

Apr 16, 2006

You posted this code and it works well

could u advise how to display just the criteria ie no heading, no :, no =, and when it displays the criteria could it fill the cell with a colour. When filter is set to "all" give a blank cell

This would over come lots complaints from operators not realizing that filters are on because they can not find the silly blue button.

Are microsoft aware of is and are they changing it in the new release.

View 9 Replies View Related

Autofilter With Variable Criteria

May 16, 2006

I'm actually trying to write a Macro that applies autofilters and the information just doesn't show! The autofilter is properly applied and everything, but the only visible row is the header. The code is the following.

mes = Month([M2])
filfecha = Range("M2").Text
Columns("E").AutoFit
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:=filfecha

View 2 Replies View Related

Autofilter Criteria From Textbox

Mar 5, 2007

I would like to do some search function. As I know, to have "contains" in criteria, I can simply add *. But then, the asterisk(*) doesnt work with forms. I want my user to put the word they are looking for in a form then the system will select from a different worksheet. Below is my coding and bold is my biggest problem.

Sub Advanced_Find()
company = Workbooks("CONTRACT").Sheets("Advanced Find"). Cells(9, 3).Value
Workbooks("CONTRACT").Sheets("Sheet2").Activate
Range("C3"). AutoFilter Field:=3, Criteria1:=*company*, Operator:=xlAnd
Sheets("Sheet2").Rows(3).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Application.WindowState = xlMinimized
Workbooks("CONTRACT").Sheets("Advanced Find").Activate
Workbooks("CONTRACT").Sheets("Advanced Find").Cells(15, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub()

View 2 Replies View Related

Comparing Autofilter Criteria

Mar 16, 2007

I want to write a VBA routine to compare the Autofilter criteria found on two different worksheets. If the criteria is not the same I will display an error message. The range the Autofilter is working on could be one to many columns. The criteria could be anything from selecting a value from the autofilter list to multiple columns using a custom autofilter with multiple conditions.

View 2 Replies View Related

Range Name In Autofilter Criteria

Jun 22, 2007

I'm trying to make the criteria in an autofilter bit of code be the value of a cell in my spreadhseet. I have named the cell and would like to reference the named range rather than the cell address if possible.

Exp = Range("ExpenseGroup")

Sheets("Sheet2").Select
Selection.AutoFilter Field:=2, Criteria1:=Exp
Sheets("KPICharts").Select
Range("A1").Select

ExpenseGroup is my range which is located on sheet "cntrl" cell "G5"

When I run my code it referes to line 1 and says" Function call on lefthand side of assignment must return a Varient or Object".

View 8 Replies View Related

Display Criteria Of Autofilter

Dec 31, 2007

I have the following code based on Multiple user-selected dropdowns. What I need to add is I believe a Volatile Application, where if the user chooses the option "ALL" OR leaves blank (doesn't pick anything from the dropdown,) the respective column does not get Autofilter applied. I have 6 different criteria, including a Start Date and End Date. So, the final result I'm trying to get is to have autofilter applied to only those columns for which the user selected a specific option.

Dim dDate As Date
Dim strDate As String
Dim lDate As Long
Dim rDate As Range

Worksheets("Data").AutoFilterMode = False
Set rDate = Sheets("Summary").Range("B3")

If Not IsDate(rDate) Then 'Check if valid
MsgBox "Non valid date and time"
Exit Sub
End If

View 3 Replies View Related

Autofilter More Than 2 Criteria On Strings With Wildcard

Mar 12, 2013

I am trying to autofilter more than 2 criteria, for example 3 criteria.

Below code works fine as the criteria are exact match strings

VB:
ActiveSheet.Range("$A$1:$O$25").AutoFilter Field:=7, Criteria1:=Array( _
"=apple", "=orange", "=grape"), Operator:=xlFilterValues

However below code does not work when the criteria are strings with wildcard "*"

VB:
ActiveSheet.Range("$A$1:$O$25").AutoFilter Field:=7, Criteria1:=Array( _
"=*apple*", "=*orange*", "=*grape*"), Operator:=xlFilterValues

I need a wildcard * indicating strings contains instead of exact match the criteria.

Data looks like this

Fruits
apple, orange, peach
orange, strawberry
banana, peach
grape, peach

After autofilter, any rows that contains one of the criteria(in this case orange, apple, grape) will remain.

View 1 Replies View Related

Storing Autofilter Criteria In A Range

Sep 17, 2013

I would like to know that is there any way of recording criteria when I filtered data.

For example; A1,A2,A3,A4,A5,A6,A7,A8,A9,A10 and A11

I have this data such as Data,1,2,3,4,5,6,7,8,9,10 (starting from Range A2). Now when I created auto filter as

VB:
ActiveSheet.Range("$A$2:$A$11" _
).AutoFilter Field:=1, Criteria1:=Array("2", "3", "4", "5"), Operator:=xlFilterValues

And this criteria can be changed by the user anytime. Now for some reason sometimes I trigger a code which works under Function key(F8) and it refresh some data from the database. Just before it gets the data, it will remove the filter such as :

VB:
ActiveSheet.Range("$A$1:$A$11" _
).AutoFilter Field:=1

My problem is after I run my code (under F8), I want to filter back with the same criteria. Is there anyway that i record my criteria in any cell whenever I filter?

View 2 Replies View Related

VBA Autofilter Using Only Year Or Month As Criteria?

Feb 4, 2014

I have a Excel-spreadsheet (sheet1) which has dates in column G. I am looking for autofiltering data by year or month based on date value in column G. In my userform I have two comboboxes, one has years from 2009-2014 and another one are months from Jan-Dec. How can I write a code that shows only rows which match either year or month value based on combobox selection?

View 3 Replies View Related

Autofilter Criteria With Array NOT EQUAL

May 23, 2013

Filter below:

Code:

Sheets("Sheet1").Range("$A$3:$AO$64999").AutoFilter Field:=1,
Criteria1:=Array("0" _, "1", "2", "3", ""), Operator:=xlFilterValues

Now I would like to change to exclude these values, tried some tricks, like:

Criteria1:=Array("0" _, "1", "2", "3", ""), Operator:=xlFilterValues

but not working.

View 2 Replies View Related

Autofilter Criteria Based On Listbox Value

Apr 30, 2009

I populated a listbox with unique items from a list. I am trying to the get result of that listbox to goto Sheet2 and sort the table ( named: data_area) on that sheet. Code using below and attached file with example.

Code from the Userform to execute ....

View 9 Replies View Related

Show AutoFilter Criteria In Cell

Aug 6, 2006

I attached a small file in which I filtered on Cities. And now I am looking for a VBA to copy the filter to an empty cell. Is this possible?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved