Excel VBA Autofilter: Um Guia Completo com Exemplos

Muitas funcionalidades do Excel também estão disponíveis para serem usadas em VBA – e o método Autofiltro é uma dessas funcionalidades.

Se você tiver um conjunto de dados e quiser filtrá-lo usando um critério, você pode facilmente fazê-lo usando a opção Filtro na faixa de dados.

E se você quiser uma versão mais avançada dele, há um filtro avançado no Excel também.

Então Por que mesmo usar o AutoFilter em VBA?

Se você só precisa filtrar os dados e fazer algumas coisas básicas, eu recomendaria ficar com a funcionalidade de Filtro embutido que a interface do Excel oferece.

Você deve usar o Autofiltro em VBA quando quiser filtrar os dados como parte da sua automação (ou se ele ajudar a economizar tempo, tornando-o mais rápido para filtrar os dados).

Por exemplo, suponha que você queira filtrar rapidamente os dados com base em uma seleção drop-down, e depois copiar esses dados filtrados em uma nova planilha.

Embora isso possa ser feito usando a funcionalidade de filtro embutida junto com um pouco de copy-paste, pode levar muito tempo para fazer isso manualmente.

Em tal cenário, usando o VBA Autofilter pode acelerar as coisas e poupar tempo.

Nota: Vou cobrir este exemplo (sobre filtragem de dados com base em uma seleção drop-down e cópia em uma nova planilha) mais tarde neste tutorial.

Sintaxe do Autofiltro do Excel VBA

Expression. AutoFilter( _Field_ , _Criteria1_ , _Operator_ , _Criteria2_ , _VisibleDropDown_ )

• Expressão: Este é o intervalo no qual você quer aplicar o filtro automático.
• Campo: Este é o número da coluna que você quer filtrar. Este é contado a partir da esquerda no conjunto de dados. Então se você quiser filtrar dados com base na segunda coluna, este valor seria 2.
• Critério1: Este é o critério baseado no qual você quer filtrar o conjunto de dados.
• Operador: Caso você esteja usando o critério 2 também, você pode combinar estes dois critérios com base no Operador. Os seguintes operadores estão disponíveis para uso: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Critério2: Este é o segundo critério no qual você pode filtrar o conjunto de dados.
• VisibleDropDown: Você pode especificar se deseja ou não que o ícone drop-down do filtro apareça nas colunas filtradas. Este argumento pode ser VERDADEIRO ou FALSO.

Parte da Expressão, todos os outros argumentos são opcionais.

No caso de você não usar nenhum argumento, ele simplesmente aplicaria ou removeria os ícones de filtro às colunas.

Sub FilterRows()Worksheets("Filter Data").Range("A1").AutoFilterEnd Sub

O código acima simplesmente aplicaria o método Autofiltro às colunas (ou se ele já estiver aplicado, ele o removerá).

Isso significa simplesmente que se você não puder ver os ícones de filtro nos cabeçalhos das colunas, você começará a vê-lo quando este código acima for executado, e se você puder vê-lo, então ele será removido.

Caso você tenha algum dado filtrado, ele removerá os filtros e mostrará o conjunto de dados completo.

Agora vamos ver alguns exemplos de como usar o Autofiltro VBA do Excel que tornará seu uso claro.

Exemplo: Filtrando Dados com base em uma condição de Texto

Se você tiver um conjunto de dados como mostrado abaixo e quiser filtrá-lo com base na coluna ‘Item’.

O código abaixo filtraria todas as linhas onde o item é ‘Impressora’.

Sub FilterRows()Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer"End Sub

O código acima refere-se a Folha1 e dentro dela, refere-se a A1 (que é uma célula no conjunto de dados).

Nota que aqui usamos Field:=2, pois a coluna item é a segunda coluna em nosso conjunto de dados da esquerda.

But, antes de mostrar-lhe esses, deixe-me primeiro cobrir alguns exemplos para mostrar-lhe o que todo o método AutoFilter pode fazer.

Clique aqui para baixar o arquivo de exemplo e siga.

Exemplo: Vários Critérios (AND/OR) na Mesma Coluna

Suponha que tenho o mesmo conjunto de dados, e desta vez quero filtrar todos os registros onde o item é ‘Impressora’ ou ‘Projetor’.

O código abaixo faria isso:

Sub FilterRowsOR()Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer", Operator:=xlOr, Criteria2:="Projector"End Sub

Nota que aqui eu usei o operador xlOR.

Isto diz ao VBA para usar ambos os critérios e filtrar os dados se algum dos dois critérios for satisfeito.

Simplesmente, você também pode usar o critério AND.

Por exemplo, se você quiser filtrar todos os registros onde a quantidade é maior que 10 mas menor que 20, você pode usar o código abaixo:

Sub FilterRowsAND()Worksheets("Sheet1").Range("A1").AutoFilter Field:=4, Criteria1:=">10", _ Operator:=xlAnd, Criteria2:="<20"End Sub

Exemplo: Critérios Múltiplos Com Colunas Diferentes

Suponha que você tenha o seguinte conjunto de dados.

Com o Autofiltro, você pode filtrar várias colunas ao mesmo tempo.

Por exemplo, se você quiser filtrar todos os registros onde o item é ‘Impressora’ e o Representante de Vendas é ‘Marca’, você pode usar o código abaixo:

Sub FilterRows()With Worksheets("Sheet1").Range("A1").AutoFilter field:=2, Criteria1:="Printer".AutoFilter field:=3, Criteria1:="Mark"End WithEnd Sub

Exemplo: Filter Top 10 Records Using the AutoFilter Method

Suppose you have the below dataet.

Below is the code that will give you the top 10 records (based on the quantity column):

Sub FilterRowsTop10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10ItemsEnd Sub

No código acima, eu usei o ActiveSheet. Você pode usar o nome da folha se quiser.

Note que neste exemplo, se você quiser obter os 5 primeiros itens, basta alterar o número no Criteria1:=”10″ de 10 para 5.

Então, para os 5 primeiros itens, o código seria:

Sub FilterRowsTop5()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="5", Operator:=xlTop10ItemsEnd Sub

Pode parecer estranho, mas não importa quantos itens de topo você queira, o valor do operador sempre permanece xlTop10Items.

Simplesmente, o código abaixo lhe daria os 10 itens inferiores:

Sub FilterRowsBottom10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlBottom10ItemsEnd Sub

E se você quiser os 5 itens inferiores, altere o número no Critério1:=”10″ de 10 para 5.

Exemplo: Filter Top 10 Percent Using the AutoFilter Method

Suppose you have the same data set (as used in the previous examples).

Below is the code that will give you the top 10 percent records (based on the quantity column):

Sub FilterRowsTop10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10PercentEnd Sub

In our dataet, since we have 20 records, it will return the top 2 records (which is 10% of the total records).

Example: Usando caracteres curinga no Autofiltro

Suponha que você tenha um conjunto de dados como mostrado abaixo:

Se você quiser filtrar todas as linhas onde o nome do item contém a palavra ‘Quadro’, você pode usar o código abaixo:

Sub FilterRowsWildcard()Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="*Board*"End Sub

No código acima, eu usei o caractere curinga * (asterisco) antes e depois da palavra ‘Quadro’ (que é o critério).

Um asterisco pode representar qualquer número de caracteres. Então isto filtraria qualquer item que tenha a palavra ‘Board’ nele.

Exemplo: Copy Filtered Rows into a New Sheet

Se você quiser não só filtrar os registros com base em critérios mas também copiar as linhas filtradas, você pode usar a macro abaixo.

Copia as linhas filtradas, adiciona uma nova planilha e depois cola essas linhas copiadas na nova planilha.

Sub CopyFilteredRows()Dim rng As RangeDim ws As WorksheetIf Worksheets("Sheet1").AutoFilterMode = False ThenMsgBox "There are no filtered rows"Exit SubEnd IfSet rng = Worksheets("Sheet1").AutoFilter.RangeSet ws = Worksheets.Addrng.Copy Range("A1")End Sub

O código acima verificaria se existem linhas filtradas na Folha1 ou não.

Se não existirem linhas filtradas, mostrará uma caixa de mensagem dizendo que.

E se existirem linhas filtradas, irá copiá-las, inserir uma nova planilha, e colar essas linhas naquela planilha recém inserida.

Exemplo: Filter Data based on a Cell Value

Using Autofilter in VBA together with a drop-down list, you can create a functionality where as soon as you select an item from the drop-down, all the records for that item are filtered.

Something as shown below:

Click here to download the example file and follow along.

Este tipo de construção pode ser útil quando você quiser filtrar dados rapidamente e depois usá-los mais tarde no seu trabalho.

Below é o código que fará isso:

Private Sub Worksheet_Change(ByVal Target As Range)If Target.Address = "$B" Then If Range("B2") = "All" Then Range("A5").AutoFilter Else Range("A5").AutoFilter Field:=2, Criteria1:=Range("B2") End IfEnd IfEnd Sub

Este é um código de evento da planilha, que só é executado quando há uma mudança na planilha e a célula alvo é B2 (onde temos a drop-down).

Also, uma condição If Else é usada para verificar se o usuário selecionou ‘All’ na drop-down. Se All for selecionado, todo o conjunto de dados é mostrado.

Este código NÃO é colocado em um módulo.

Em vez disso, ele precisa ser colocado no backend da planilha que tem estes dados.

Aqui estão os passos para colocar este código na janela de código da planilha:

1. Abra o Editor VB (atalho de teclado – ALT + F11).
2. No painel Project Explorer, clique duas vezes no nome da planilha na qual você deseja esta funcionalidade de filtragem.
3. Na janela de código da planilha, copie e cole o código acima.
4. Fecha o Editor VB.

Agora, quando você usar a lista suspensa, ele irá filtrar automaticamente os dados.

Este é um código de evento de planilha, que só é executado quando há uma mudança na planilha e a célula de destino é B2 (onde temos a drop-down).

Também, uma condição If Else é usada para verificar se o usuário selecionou ‘All’ na drop-down. Se All estiver selecionado, todo o conjunto de dados é mostrado.

Ativar ou desativar o AutoFiltro do Excel usando VBA

Ao aplicar o Autofiltro a um intervalo de células, pode já haver alguns filtros no lugar.

Você pode usar o código abaixo para desativar qualquer filtro automático pré-aplicado:

Sub TurnOFFAutoFilter() Worksheets("Sheet1").AutoFilterMode = FalseEnd Sub

Este código verifica as planilhas inteiras e remove qualquer filtro que tenha sido aplicado.

Se não quiser desligar filtros da folha inteira mas apenas de um conjunto de dados específico, use o código abaixo:

Sub TurnOFFAutoFilter() If Worksheets("Sheet1").Range("A1").AutoFilter Then Worksheets("Sheet1").Range("A1").AutoFilter End IfEnd Sub

O código acima verifica se já existem filtros no lugar ou não.

Se já existem filtros aplicados, remove-os, senão não faz nada.

Simplesmente, se você quiser ligar o AutoFilter, use o código abaixo:

Sub TurnOnAutoFilter() If Not Worksheets("Sheet1").Range("A4").AutoFilter Then Worksheets("Sheet1").Range("A4").AutoFilter End IfEnd Sub

Verifique se o AutoFilter já está aplicado

Se você tiver uma folha com vários conjuntos de dados e quiser ter certeza de que não há filtros já instalados, você pode usar o código abaixo.

Sub CheckforFilters()If ActiveSheet.AutoFilterMode = True ThenMsgBox "There are Filters already in place"ElseMsgBox "There are no filters"End IfEnd Sub

Este código usa uma função de caixa de mensagens que exibe uma mensagem ‘There are Filters already in place’ quando encontra filtros na folha, caso contrário mostra ‘There are no filters’.

Mostra todos os dados

Se você tiver filtros aplicados ao conjunto de dados e quiser mostrar todos os dados, use o código abaixo:

Sub ShowAllData()If ActiveSheet.FilterMode Then ActiveSheet.ShowAllDataEnd Sub

O código acima verifica se o FilterMode é VERDADEIRO ou FALSO.

Se for verdade, significa que um filtro foi aplicado e usa o método ShowAllData para mostrar todos os dados.

Note que isto não remove os filtros. Os ícones de filtro ainda estão disponíveis para serem usados.

Usando o AutoFilter em folhas protegidas

Por padrão, quando você protege uma folha, os filtros não funcionam.

Caso você já tenha filtros instalados, você pode habilitar o AutoFilter para garantir que ele funcione mesmo em folhas protegidas.

Para fazer isso, marque a opção Usar Autofiltro enquanto protege a folha.

Embora isso funcione quando você já tiver filtros instalados, caso você tente adicionar Autofiltros usando um código VBA, ele não funcionará.

Desde que a folha esteja protegida, ele não permitirá que nenhuma macro seja executada e faça alterações no Autofiltro.

Então você precisa usar um código para proteger a planilha e ter certeza que os filtros automáticos estão habilitados nela.

Isso pode ser útil quando você tiver criado um filtro dinâmico (algo que eu cobri no exemplo – ‘Filter Data based on a Cell Value’).

Below é o código que irá proteger a folha, mas ao mesmo tempo, permite que você use Filtros assim como macros VBA nela.

Private Sub Workbook_Open()With Worksheets("Sheet1").EnableAutoFilter = True.Protect Password:="password", Contents:=True, UserInterfaceOnly:=TrueEnd WithEnd Sub

Este código precisa ser colocado na janela de código ThisWorkbook.

Aqui estão os passos para colocar o código na janela de código ThisWorkbook:

1. Abra o Editor VB (atalho de teclado – ALT + F11).
2. No painel Project Explorer, clique duas vezes no objeto ThisWorkbook.
3. Na janela de código que se abre, copie e cole o código acima.

A partir do momento em que você abrir a pasta de trabalho e ativar as macros, ela executará a macro automaticamente e protegerá Sheet1.

No entanto, antes de fazer isso, ele irá especificar ‘EnableAutoFilter = True’, o que significa que os filtros também funcionariam na folha protegida.

Também, ele define o argumento ‘UserInterfaceOnly’ para ‘True’. Isto significa que enquanto a planilha está protegida, o código de macros VBA continuaria a funcionar.

Pode também gostar dos seguintes tutoriais VBA:

• Excel VBA Loops.
• Filtrar Células com Formatação de Fontes em Negrito.
• Gravar uma Macro.
• Ordenar Dados Usando VBA.
• Ordenar Pastas de Trabalho no Excel.