Hello, All!
I need set more two condition in AutoFilter. How i can do it?
Range ("A1:E1") .Select Select ion. AutoFilter Selection. AutoFilter
Field:=4,
Criterial : ="=str1" , Operator : =xlOr ,
Criteria2 :="=str2"
<<<Criteria3 :="=str3">>>
PS Sorry, my english is bad.
You need to look through your Excel help files for "Advanced Filtering
Criteria". Here's what I've got in Excel XP for the topic:
Filter by using advanced criteria
Insert at least three blank rows above the list (list: A series of
worksheet rows that contain related data, such as an invoice database
or a set of client names and phone numbers. The first row of the list
has labels for the columns.) that can be used as a criteria (criteria:
Conditions you specify to limit which records are included in the
result set of a query or filter. For example, the following criterion
selects records for which the value for the Order Amount field is
greater than 30,000: Order Amount > 30000.) range. The criteria range
must have column labels. Make sure there is at least one blank row
between the criteria values and the list.
What this might look like
Type Salesperson Sales
Type Salesperson Sales
Beverages Suyama 5122
Meat Davolio 450
Produce Buchanan 6328
Produce Davolio 6544
In the rows below the column labels, type the criteria you want to
match.
Criteria examples
Multiple conditions in a single column
If you have two or more conditions for a single column, type the
criteria directly below each other in separate rows. For example, the
following criteria range displays the rows that contain either
"Davolio," "Buchanan," or "Suyama" in the Salesperson column.
Salesperson
Davolio
Buchanan
Suyama
One condition in two or more columns
To find data that meets one condition in two or more columns, enter
all the criteria in the same row of the criteria range. For example,
the following criteria range displays all rows that contain "Produce"
in the Type column, "Davolio" in the Salesperson column, and sales
values greater than $1,000.
Type Salesperson Sales
Produce Davolio >1000
One condition in one column or another
To find data that meets either a condition in one column or a
condition in another column, enter the criteria in different rows of
the criteria range. For example, the following criteria range displays
all rows that contain either "Produce" in the Type column, "Davolio"
in the Salesperson column, or sales values greater than $1,000.
Type Salesperson Sales
Produce
Davolio
One of two sets of conditions for two columns
To find rows that meet one of two sets of conditions, where each set
includes conditions for more than one column, type the criteria in
separate rows. For example, the following criteria range displays the
rows that contain both "Davolio" in the Salesperson column and sales
values greater than $3,000, and also displays the rows for salesperson
Buchanan with sales values greater than $1,500.
Salesperson Sales
Davolio >3000
Buchanan >1500
More than two sets of conditions for one column
To find rows that meet greater than two sets of conditions, include
multiple columns with the same column heading. For example, the
following criteria range displays sales that are between 5,000 and
8,000 in addition to sales that are less than 500.
Sales Sales
<500
Conditions created as the result of a formula
You can use a calculated value that is the result of a formula
(formula: A sequence of values, cell references, names, functions, or
operators in a cell that together produce a new value. A formula
always begins with an equal sign (=).) as your criterion. When you use
a formula to create a criterion, do not use a column label for a
criteria label; either keep the criteria label blank or use a label
that is not a column label in the list. For example, the following
criteria range displays rows that have a value in column C greater
than the average of cells C7:C10.
=C7>AVERAGE($C$7:$C$10)
Notes
The formula you use for a condition must use a relative reference
(relative reference: In a formula, the address of a cell based on the
relative position of the cell that contains the formula and the cell
referred to. If you copy the formula, the reference automatically
adjusts. A relative reference takes the form A1.) to refer to the
column label (for example, Sales) or the corresponding field in the
first record. All other references in the formula must be absolute
references (absolute cell reference: In a formula, the exact address
of a cell, regardless of the position of the cell that contains the
formula. An absolute cell reference takes the form $A$1.), and the
formula must evaluate to TRUE or FALSE. In the formula example, "C7"
refers to the field (column C) for the first record (row 7) of the
list.
You can use a column label in the formula instead of a relative cell
reference or a range name. When Microsoft Excel displays an error
value such as #NAME? or #VALUE! in the cell that contains the
criterion, you can ignore this error because it does not affect how
the list is filtered.
When evaluating data, Microsoft Excel does not distinguish between
uppercase and lowercase characters.
All items that begin with that text are filtered. For example, if you
type the text Dav as a criterion, Microsoft Excel finds "Davolio,"
"David," and "Davis." To match only the specified text, type the
following formula, where text is the text you want to find. =''=text''
To find text values that share some characters but not others, use a
wildcard character.
Wildcard characters
The following wildcard characters can be used as comparison criteria
(criteria: Conditions you specify to limit which records are included
in the result set of a query or filter. For example, the following
criterion selects records for which the value for the Order Amount
field is greater than 30,000: Order Amount > 30000.) for filters, and
when searching and replacing content.
Use To find
? (question mark) Any single character
For example, sm?th finds "smith" and "smyth"
* (asterisk) Any number of characters
For example, *east finds "Northeast" and "Southeast"
~ (tilde) followed by ?, *, or ~ A question mark, asterisk, or tilde
For example, fy91~? finds "fy91?"
Click a cell in the list.
On the Data menu, point to Filter, and then click Advanced Filter.
To filter the list by hiding rows that don't match your criteria,
click Filter the list, in-place.
To filter the list by copying rows that match your criteria to another
area of the worksheet, click Copy to another location, click in the
Copy to box, and then click the upper-left corner of the area where
you want to paste the rows.
In the Criteria range box, enter the reference for the criteria range,
including the criteria labels.
To move the Advanced Filter dialog box out of the way temporarily
while you select the criteria range, click Collapse Dialog .
To change how the data is filtered, change the values in the criteria
range and filter the data again.
Tips
You can name a range Criteria, and the reference for the range will
appear automatically in the Criteria range box. You can also define
the name Database for the range of data to be filtered and define the
name Extract for the area where you want to paste the rows, and these
ranges will appear automatically in the List range and Copy to boxes,
respectively.
When you copy filtered rows to another location, you can specify which
columns to include in the copy. Before filtering, copy the column
labels for the columns you want to the first row of the area where you
plan to paste the filtered rows. When you filter, enter a reference to
the copied column labels in the Copy to box. The copied rows will then
include only the columns for which you copied the labels.
