✔ Programação GLOBAL®: VBA Excel - Filtrando Tabelas ou Listas

VBA Excel - Filtrando Tabelas ou Listas - Filtering Tables or Lists

Consultancy in Excel, Access, VBA, Dashboards, Scorecards & PowerPoint Projects

DESENVOLVO APLICAÇÕES VBA

Perguntas, Questões e Esclarecimentos

Calendário Compacto para 2014

O código a seguir filtra uma tabela ou lista. Para testá-lo usamos uma tabela semelhante à abaixo.

Basta digitar os dados e, em seguida, usar o procedimento descrito.

Sub FilterListOrTableData()

Dim ACell As Range

Dim ActiveCellInTable As Boolean

Dim FilterCriteria As String

'Check to see if the worksheet is protected.

If ActiveSheet.ProtectContents = True Then

MsgBox "This macro will not work when the worksheet is write-protected.", _

vbOKOnly, "Filter example"

Exit Sub

End If

'Set a reference to the ActiveCell named ACell. You can always use

'ACell now to point to this cell, no matter where you are in the workbook.

Set ACell = ActiveCell

'Test to see if ACell is in a table or list. Note that by using ACell.ListObject, you

'don't need to know the name of the table to work with it.

On Error Resume Next

ActiveCellInTable = (ACell.ListObject.Name <> "")

On Error GoTo 0

'If the cell is in a list or table, run the code.

If ActiveCellInTable = True Then

'Show all data in the table or list.

On Error Resume Next

ActiveSheet.ShowAllData

On Error GoTo 0

'This example filters on the first column in the List/Table

'(change the field if needed). In this case the Table starts

'in A so Field:=1 is column A, field 2 = column B, ......

'Use "<>" & filtercriteria if you want to exclude the criteria from the filter.

FilterCriteria = InputBox("What text do you want to filter on?", _

"Type in the filter item.")

ACell.ListObject.Range.AutoFilter _

Field:=1, _

Criteria1:="=" & FilterCriteria

Else

MsgBox "Select a cell in your list or table before you run the macro.", _

vbOKOnly, "Filter example"

End If

End Sub