VBA – Filters and Reporting

AutoFilter is applicable to a column or a set of columns. It filters data depending on the given criteria. The filter displays a certain number of lines based on defined criteria.

Syntax:

expression .AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)

Where

Field– It is an integer offset of the field which contains the Filter. The data type is variant, which means it can hold any Data types – integers, strings, date and so on.

Criteria1– It’s a condition based on which columns are selected.

Operator– It specifies the type of filter.

Example 1: To Close All Existing AutoFilters and Create New AutoFilters

Sub AutoFilter1()

With ActiveSheet

‘Turns off any existing AutoFilters if any..

.AutoFilterMode = False

‘ Now apply AutoFilter to a Range

.Range(“A1:C1”).AutoFilter

End With

End Sub

Example 2: Using AutoFilter to match single criteria

Sub FilterTo1Criteria()

With Sheet1

.AutoFilterMode = False

.Range(“A1:C1”).AutoFilter

.Range(“A1:C1″).AutoFilter Field:=2, Criteria1:=”North”

End With

End Sub

Leave a Reply

Your email address will not be published.

Enjoy the tutorial? Please spread the word :)

Follow by Email
Facebook
Facebook
Google+2k
Google+
http://hirdeshbhardwaj.com/2017/05/01/vba-filters-reporting/
YouTube31
YouTube