Back to Excel Newsletter Archives

EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS

** Current Special!** Complete Excel __Excel Training Course__ for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download ** Bring Your Spreadsheets to Life!**Tired of analyzing endless rows and columns of data?

turns your Excel spreadsheets into stunning dashboards and data presentations.

**Dynamic Lookups in Excel **You can **download a working sample from the here **

These are very handy for when you lookup data but cannot be sure which column your returned data should come from. In other words, users may have inserted a column within the table. The VLOOKUP is about the most popular of the Lookup functions so we will use this function for examples.

Ok, let's go with a sample using a small table of data for ease of understanding. You can **download a working sample from the here **

The layout of our data are headings in row 1 from A1:D1 on a Worksheet called "Data".**Names | Department | Age | Gender | Pay Rate P/H**

Directly underneath these headings is related data. For simplicity though, only down to row 8.

1) While on the "Data" Worksheet Go to

2) In the "Names in Workbook:" box type: Data_Table.

3) In the "Refers to:" box type, or copy/paste;

4) Click Add but not OK, just yet.

5) Repeat steps 2, 3 and 4 using the names (no quotes) and formulas below.

"

"

"

"

"

"

6) Now after clicking

It is vital that there is no other data on the "Data" Worksheet other than your table of data occupying A1:E8.

Let's now show some formulas that can be used to lookup a name in the table (Data_Table) and return the "Department" they work in.

This formula will locate the name "June K" in the 1st column of data (Names) and return the corresponding row from the "Department" column. Should a user insert a column anywhere within, or at the end of the table, the formula will still return the correct result. However, should a column be added before Column A (making "Names" become column B) it will fail. To address this potential problem we could use

This formula is the most flexible as not only will it accommodate columns being added anywhere within, before or after the table, we can also use it to look left of our Names column. This may be needed when/if a column is added to the Start of our table.

Again though, as it uses VLOOKUP it will always look in the first column only of the table (Data_Table) for the name.

This last one requires us to add some more named ranges, but we can do so with the aid of Excel.

1) Select the table of data but do not include any headings.

2) Go to

Ok, we have now created a named range for all our names. It should be noted that, as spaces are not allowed,

Now we can simply

**Current Special!** Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download,**30 Day Money Back Guarantee** & Free Excel Help for LIFE!

**Back To: AutoFilters in Excel VBA . See Also: AutoFilters via User Interface | Display Excel AutoFilter Criteria **

**VBA & AutoFilters Criteria**

Ok, **last page ** we looked at how we can set AutoFilters up within an Excel Workbook. In summary, rather than check if AutoFilters are already applied to a specified Worksheet with an IF Statement, then, if they were on and in use (filtered down) we would turn them off and apply to the needed range. If they weren't on then simply apply them to the needed range.

This however was a lot of superfluous code. The easiest and best way is as shown below;

Sub ApplyAutoFilters()With ActiveSheet .AutoFilterMode = False .Range("A1:D1").AutoFilter End WithEnd Sub

In the code above we turn off any existing AutoFilters and apply them to the range A1:D1 of the active worksheet.**FILTERING DOWN TO SHOW 1 MATCHING CRITERIA**

Let's now look at how we can apply AutoFilters and show only matching criteria. In the examples below I have used a specified Worksheet by referencing its CodeName . It is also based on the data being in the range A1:D100 with A1:D1 being headings:

Sub FilterTo1Criteria()With Sheet1 .AutoFilterMode = False .Range("A1:D1").AutoFilter .Range("A1:D1").AutoFilter Field:=2, Criteria1:=35 End WithEnd Sub

In the example below we have filtered our table down to match 1 criteria (Criteria1) on our second heading (Age) to show only those who are 35. If we were to show all those that are 35 or older our Criteria1 would need to be like;**Criteria1:=">=35"**

In other words, the criteria and any operators should be passed as text with an equal sign preceding the string.

We can have the filter show only blanks for the specified Field by using: **Criteria1:="="**

To show all non-blanks we would use: **Criteria1:="<>"****XlAutoFilterOperator can be one of these constants**

xlAnd default

xlBottom10Items

xlBottom10Percent

xlOr

xlTop10Items

xlTop10Percent

If we wanted to show only those in the Name field whose name Start s with a "D" we would use: **Criteria1:="=D*"**

To show all names that do not contain a letter "a" we would use: **Criteria1:="<>*a*"**

In short, the best way to obtain your needed criteria is to simply record a macro filtering your table down and then copy the **Criteria1:** and the optional **Criteria2:** code generated.

If desired, for whatever reason, we can have Excel hide the Filter arrow for Field2 (or any Field) by using an additional

argument after Criteria1. That is: ,**VisibleDropDown:=False****FILTERING DOWN TO SHOW 2 MATCHING CRITERIA**

Let's now expand on the above by filtering down to show 2 criteria.

Sub FilterTo2Criteria()With Sheet1 .AutoFilterMode = False .Range("A1:D1").AutoFilter .Range("A1:D1").AutoFilter Field:=2, Criteria1:=">=35", _ Operator:=xlAnd, Criteria2:="<=45" End WithEnd Sub

In the above code we have chosen to show all whose age is between 35 and 45. It's important to note that for the **Operator** argument we have used **xlAnd**. If we had used the other choice (**XlOr**) our results would be that of our original table. That is, all records would show as all people would be either** >=35 or <=45**.

Sub FilterTo2Fields()With Sheet1 .AutoFilterMode = False With .Range("A1:D1") .AutoFilter .AutoFilter Field:=1, Criteria1:="Dave" .AutoFilter Field:=4, Criteria1:="Lab" End With End WithEnd Sub

In the code above we have shown all those with the name "Dave" whose department is "Lab". As you can see from the above code,

We can add more fields, but cannot exceed our total column count of headings. In this case we could use Field 1, 2, 3 and/or 4.**FILTERING DOWN TO SHOW WILDCARDS**

The wildcard characters we can use in AutoFilter are the asterisk (*) to represent a string of characters and/or the question mark (?) to represent a single character.

However, what if we need to show data that actually houses the * or ? By the way, if at all possible these characters should not be used on their own.

As you can see from the above code, we have told Excel we actually want to filter by the asterisk and not have it seen as a wildcard. The same applies for the question mark. That is:Sub FilterToShowAsterisk()With Sheet1 .AutoFilterMode = False .Range("A1:D1").AutoFilter .Range("A1:D1").AutoFilter Field:=1, Criteria1:="~*" End WithEnd Sub

**Back To: AutoFilters in Excel VBA **

**Software Categories **Search Software

**Software Categories ** Search Software

Excel **Add-ins** || Excel **Training ** || Excel **Templates** || **Employee Scheduling Software** || Excel** Password Recovery** and Access & Word etc|| Excel Recovery and Access & Word etc || **Financial** Software || Financial **Calculators** || **Conversion** Software || **Construction** **Estimating** Software || **Drilling Software** || **Real Estate** Investment Software || Time** Management** Software || **Database** Software || **Neural** Network Software || **Trading** Software || **Charting** Software || **Windows** & **Internet** Software || ** Barcodes ** Fonts, ActiveX, Labels and DLL's || **Code Printing** Software || **Outlook** Add-ins

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.