AutoFilter Criteria in Excel VBA

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 With
  
End 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:

Name | Age | Date Joined | Department

Sub FilterTo1Criteria()

   With Sheet1
           .AutoFilterMode = False
           .Range("A1:D1").AutoFilter
           .Range("A1:D1").AutoFilter Field:=2, Criteria1:=35
   End With
  
End 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 With
  
End 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 With   
End 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.

Sub FilterToShowAsterisk()

   With Sheet1
           .AutoFilterMode = False
           .Range("A1:D1").AutoFilter
           .Range("A1:D1").AutoFilter Field:=1, Criteria1:="~*"
   End With
  
End Sub

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: Criteria1:="~?"

VBA & AutoFilters

AutoFilter provides us with a MUCH faster alternative to loops of all kinds.

In the majority of cases it's faster and more efficient to use one of Excel's built in features as apposed to re-inventing the wheel with VBA code. This is why those that have learnt Excel from the ground-up know what native features Excel has to offer. While those only familiar with VB/VBA tend to be the ones who re-invent the wheel.

Ok, the first thing we need to know is how to apply AutoFilter to a range. When we do apply AutoFilter via VBA one SHOULD always turn the off any current filters and remove them completely. Why not check if the AutoFilter is already in place and go from there? The answer is simple, while we can determine if AutoFilter has/is on a specific Worksheet, we cannot guarantee (with extra checking) that it is in use on the range we need! For example, we could use the code below to check.

Sub CheckForAutoFilters()
If ActiveSheet.AutoFilterMode = True Then
      MsgBox "They are visible"
Else
      MsgBox "They are not visible"
End If
End Sub

From the code above we will know if AutoFilters are visible, but not necessarily in Filter mode (more on that soon). However, we cannot tell if the AutoFilterMode is applied to the correct range. Let's now see how we can determine if the AutoFilters and in use and are being used to filter down.

Sub CheckForAutoFilters2()

With ActiveSheet
  If .AutoFilterMode = True And .FilterMode = True Then
   MsgBox "They are visible and in use"
  ElseIf .AutoFilterMode = True Then
   MsgBox "They are visible but not in use"
  Else
   MsgBox "They are not visible or in use"
  End If
End With

End Sub

As you can see, we have used the FilterMode Property of the Worksheet to determine whether the AutoFilters are filtering data down. So, in summary, AutoFilterMode tells us if the AutoFilter arrows are visible and FilterMode tells us if they are in use. However, as I mentioned above this does not tell us which range has had AutoFilter applied. So, with this in mind, we are better off simply removing any existing Autofilter and then applying them to our required range. Here is how, assuming we want A1:D1 to have the AutoFilters.

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

Another advantage to applying AutoFilter is this manner is that no error occurs if AutoFilterMode is already false. By the way, we cannot use: AutoFilterMode = True to apply AutoFilters. To apply AutoFilter (at this time with no criteria) we would use Range("A1:D1").AutoFilter. If we are to first check the range that AutoFilter is applied to, we would use code like below;

Sub IsAutoFiltersOnRightRange()
With ActiveSheet
  If .AutoFilterMode = True Then
   MsgBox .AutoFilter.Range.Address
  Else
   MsgBox "AutoFilters are not on"
  End If
 End With
End Sub

In my mind though, this code is superfluous when compared with simply removing and applying AutoFilters. Let's now look at how we apply AutoFilter to a SINGLE cell in a range. If we had our table in the range A1:D200 on the Active sheet and we used the "ApplyAutoFilters" Procedure with .Range("A1").AutoFilter we would likely end up with AutoFilter applied to ALL contiguous headings across row 1. This due to the fact that Excel will detect the contiguous headings across row 1 and assume that we want all headings to have AutoFilters. We can force Excel to not do this by specifying a 2 row single column range. For example;

Sub ApplyAutoFiltersToOneCell()
 With ActiveSheet
  .AutoFilterMode = False
  .Range("A1:A2").AutoFilter
 End With
End Sub

Add Worksheets to Excel

Adding worksheets to Excel is very simple. For example, to add a Worksheet after the active sheet (default unless stated otherwise), name it "MySheet" and have it become the active sheet, you would use some code like shown below;

Sub AddWorksheet()
Worksheets.Add().Name = "MySheet"
End Sub

If we wanted to add a Worksheet as the last Worksheet and name it "MySheet" we would use;

Sub AddAsLastWorksheet()
Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = "MySheet"
End Sub

The Add Method as it applies to the Worksheet Object also has a Before Variant as well as an After Variant. However, we can only nominate a Before or After Variant, or omit the Argument altogether. If we do omit the Before and After Variants Excel places the Worksheet after the current active Sheet.

To add, say, 4 Worksheets we could use the Count Variant;

Sub AddXWorksheets()
Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=4
End Sub

The only other Variant we can use if desired is the Type Variant. The Type specifies the sheet type. Can be one of the following XlSheetType constants: xlWorksheet, xlChart, xlExcel4MacroSheet, or xlExcel4IntlMacroSheet. If you are inserting a sheet based on an existing template, specify the path to the template (Recording a macro is best for this). The default value is xlWorksheet

Security in Excel

Level 1 - To protect your excel files, there are a few steps required to make it more difficult for other users to by pass security. To prevent changes made to the worksheet, you need to protect your worksheet. See protecting sheets. To prevent sheets from being renamed, moved or deleted, protect the workbook. However protection of worksheets and workbook can easily be hacked using macros as shown by an Excel developer. I believe the next level of protection is protecting your macros. To protect your macros, point at your project in the explorer window, right click on it and select VBA project properties, click on the Protection tab, check on Lock Project for Viewing and next key in your password and you're done. Now the project cannot be viewed or amended.

Level 2 - The next step is to force the user to enable your macro when opening your file. The best way is to use a macro to hide the important sheets (see Hiding sheets) when saving your file. Upon opening the file, a macro will be used to unhide these sheets. If the user disables the macros when opening the worksheet, they will not be able to view your worksheet unless they allow the macro to run.

Level 3 - The final step is to put an expiry date for your worksheet or your macro. However this has a draw back as the user may change the system date of the computer to by pass the step. Alternatively you can use a counter (Refer Counter Macro) to allow a fixed number of access to your worksheet or macro. Here you need to save the counter value each time the file or macro is used. Upon reaching the defined limit, disable the macro or disable the access of your worksheet.

The steps mentioned above are not 100% fool proof. But it will keep normal users out but not hackers and crackers. Here I will not supply the code as this can be lengthy and may be difficult to understand but I believe these steps may be useful to some of you out there.

Running A Sub Routine

To run another macro from within a macro you need to use the Call statement.

Sub Macro1()
Msgbox("This is Macro1")
Call Macro2 'This calls for Macro2 to run
End Sub

Saving a file

There are times you may want a macro to save a file automatically after running a macro. The second macro will save the file with a name called "MyFile". You may specify the path if you need to.

Sub Save()
ActiveWorkbook.Save
End Sub
Sub SaveName()
ActiveWorkbook.SaveAs Filename:="C:\MyFile.xls"
End Sub

Resizing a Range

Resizing a range is simple. You can apply this to inserting rows & columns or to expand a selected range. This macro resizes the range to 7 rows by 7 columns.

Sub ResizeRng()
Selection.Resize(7,7).Select
End Sub