Sub AutoFilterCustom()
'
'Prg : AutoFilterCustom
'Author : Markmzz
'Date : 06/07/2011
'Version: 01
'Configures the Autofilter only for
'columns Cod, Company and Contact
'(columns 1, 2 and 3) and filter the data
'by field Bank by criteria: CITI, JPM, RBS and BONY
'Define the macro variables
Dim FinalRow, FinalCol, myrngTeam, i As Long
Dim myRange As Range
Dim myArray() As Variant
'Activates the sheet [FONT=Arial]SecuritiesReport[/FONT]
Sheets("[FONT=Arial]SecuritiesReport[/FONT]").Activate
'Determine the number of criterias
myrngTeam = WorksheetFunction.Max( _
Range("rngTeam").Rows.Count, Range("rngTeam").Columns.Count)
ReDim myArray(1 To myrngTeam)
'Determines the last row and column
'of your input range (database)
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
FinalCol = Cells(5, Columns.Count).End(xlToLeft).Column
'References the list range
Set myRange = Range(Cells(5, 1), Cells(FinalRow, FinalCol))
'Disables and enables the Autofilter,
'for remove any previous customization,
'and enable the AutoFilter
'Enables/disables the Autofilter
myRange.AutoFilter
'Checks whether the database is not
'Active Filtered
If Not ActiveSheet.AutoFilterMode Then
'Enables the AutoFiltro
myRange.AutoFilter
End If
'Create the array CITI, JPM, RBS and BONY
i = 1
For Each myCell In Range("rngTeam")
myArray(i) = myCell.Value
i = i + 1
Next myCell
'AutoFilter the data by field Bank
'by criterion: CITI, JPM, RBS and BONY
'and disables the icon DropDown of the
'respective field
myRange.AutoFilter _
Field:=4, _
Criteria1:=myArray, _
Operator:=xlFilterValues, _
VisibleDropDown:=False
'Frees the memory used
'by object variable
Set myRange = Nothing
End Sub
