Excel Training VBA Lesson 20

AutoFilter and SpecialCells In VBA Code

 Information Helpful? Why Not Donate >> Excel Training-Video Series

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL VBA LEVEL1 TRAINING INDEX

AutoFilter and SpecialCells

I will use the Autofilter with the SpecialCells Method in some of the examples below. This is because when using the AutoFilter you will most likely only be interested with the Visible cells. Again it may be needed that we use the On Error Resume Next Statement so we don't generate a Run-time error.

AutoFilterMode

We can check to see if the AutoFilters are on by using the AutoFilterMode Property. It will return True if they are on and False if they are off.

Sub AreFiltersOn() 
    If ActiveSheet.AutoFilterMode = False Then
        MsgBox "Filters are off"
    Else
        MsgBox "Filters are on"
    End If 
End Sub

 We can also use the AutoFilterMode Property to turn off the AutoFilters by setting it to False. But we cannot turn them on by setting it to True.

We can also check to see whether a Worksheet is in FilterMode, this will return True if the Worksheet currently has hidden rows as result of the AutoFilter. This is not the same as the AutoFilterMode Property, as FilterMode would return False if the sheet had AutoFilters turned on but not in use (not filtered down), while the AutoFilterMode would return True. This Property is Read Only so we cannot change it to False if True or True if False.

The other Method that we can use is the ShowAllData. This will set the AutoFilters back to "(All)" if they are currently in use (FilterMode = True). Be careful though as a Run-time error is generated if we use this method on a sheet that is not currently filtered (FilterMode = False). To avoid this we use the FilterMode Property to check first.

Ok, so that is all the means and ways to find out the current status of a sheet with regards to AutoFilters. What we can do now is move on to actually using the AutoFilter Method in some VBA code.

Let's say we wanted to copy all the rows of a sheet that have the word "dog" in Column C and place them on another Sheet. This is where the AutoFilter Property can help us.

Sub ConditionalCopy() 
With ActiveSheet 
    If WorksheetFunction.CountIf(.Columns(3), "dog") <> 0 Then
        .AutoFilterMode = False
        .Range("A1:H1").AutoFilter
        .Range("A1:H1").AutoFilter Field:=3, Criteria1:="Dog"
        .UsedRange.SpecialCells(xlCellTypeVisible).Copy _
         Destination:=Sheet2.Range("A1")
        .AutoFilterMode = False
        Application.CutCopyMode = False
    End If 
End With 
End Sub

Let's step through this and see what we did:

1. If WorksheetFunction.CountIf(.Columns(3), "dog") <> 0 Then So the first thing we do is check to see whether the word "dog" is in Column C.

2. .AutoFilterMode = False We then set the .AutoFilterMode Property to False. We do not need to check if the AutoFilters are on because if they aren't nothing will happen. If they are on and/or in use they will be turned off.

3. .Range("A1:H1").AutoFilter We then apply the AutoFilters to the range we are interested in.

4. .Range("A1:H1").AutoFilter Field:=3, Criteria1:="Dog" We then set the Criteria Property to "Dog".

5. Field3 is Column C and is relavent to the range we filtered. .UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet2.Range("A1") Here we have used the UsedRange Property and the SpecialCells Method to copy only the cells that are visible after applying the AutoFilter. We then paste them to Sheet2.Range("A1).

6. .AutoFilterMode = False
   Application.CutCopyMode = False We then turn the filters off and clear the Clipboard.

Using this method we can easily make use of Excels Autofilter to get what we are after. What we need to do now is look at the SpecialCells Method. As I have already mentioned, this Method is the same as using Edit>Go to-Special. Recording a Macro doing just this is the best way to get the code you are wanting. Once you have done this a few times you will be able to skip the Recording bit. The text below is from the Excel help on SpecialCells and is worth taking 5 minutes to read:

SpecialCells Method

Returns a Range object that represents all the cells that match the specified type and value.

Syntax

expression.SpecialCells(Type, Value)

expression   Required. An expression that returns a Range object.

Type   Required Long. The cells to include. Can be one of the following XlCellType constants.

Value   Optional Variant. If Type is either xlCellTypeConstants or xlCellTypeFormulas, this argument is used to determine which types of cells to include in the result. These values can be added together to return more than one type. The default is to select all constants or formulas, no matter what the type. Can be one of the following XlSpecialCellsValue constants: xlErrors, xlLogical, xlNumbers, or xlTextValues.

END OF EXCEL HELP

AdvancedFilter

The last feature we will look at is the AdvancedFilter. This is feature is great for creating a list of unique items from a list. It can of course do a lot more than just this, but in the interest of keeping things simple I will only show how it can be used to create a unique list. Should you wish to go into this any deeper then I suggest Recording a Macro using this feature in the Interface and studying the code. If you have any problems or questions at all let me know and I will endeavour to help you. As with the SpecialCells Method it would pay to read the Excel help on AdvancedFilter

AdvancedFilter Method          

Filters or copies data from a list based on a criteria range. If the initial selection is a single cell, that cell's current region is used.

Syntax

expression.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)

expression   Required. An expression that returns a Range object.

Action   Required Long. The filter operation. Can be one of the following XlFilterAction constants: xlFilterInPlace or xlFilterCopy.

CriteriaRange   Optional Variant. The criteria range. If this argument is omitted, there are no criteria.

CopyToRange   Optional Variant. The destination range for the copied rows if Action is xlFilterCopy. Otherwise, this argument is ignored.

Unique   Optional Variant. True to filter unique records only. False to filter all records that meet the criteria. The default value is False.

END OF EXCEL HELP

Let's look at an example of how we would copy a list that contained non unique items and paste it as list of unique items. Assume our list is in Column A.

This code would copy the list from Column A of the ActiveSheet to Column A of Sheet2.

1. Sheet2.Columns(1).Clear This is needed as the AdvancedFilter can generate a Run-time error if the CopyToRange contains data.

2. On Error Resume Next Will prevent any Run-time error should our list not contain enough data, ie less than 2 items.

3. .Range("A1", .Range("A65536").End(xlUp)) is used to define the range in Column A, starting from cell A1 down to the very last cell in Column A that contains an entry.

This simple bit of code would give us a unique list of items on a seperate Worksheet. You may not be aware that it is not possible to copy a unique list to another sheet via the AdvancedFilter in the Excel interface. I tell you this so when/if you Record a Macro, don't try and copy to another sheet. Just copy to any old range and change the code afterwards.

Summary

So by using the above examples as a starting point it is more often than not possible to do a task in VBA that will run very quick and clean. The only limit to the built-in features of Excel is usually your own imagination. I have yet to see any code that can operate as quickly or efficiently as one of Excels built-in features. They also give you the advantage of being able to Record the code needed to use the features. But remember, you should modify the code to become efficient! You will find that once you have been able to use one of Excels built-in features to do what you want, you will start thinking of other areas it can be adopted. I quite often will take an hour or more to rack my brains trying to come up with a efficient piece of code that fully utilises one of Excels built-in features. Once I have the idea it usually only takes a few minutes to put in place.

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL VBA LEVEL1 TRAINING INDEX