Muitas funcionalidades do Excel também estão disponíveis para serem usadas no VBA - e o Autofiltro método é uma dessas funcionalidades.
Se você tiver um conjunto de dados e quiser filtrá-lo usando um critério, poderá fazer isso facilmente usando a opção Filtro na faixa de opções Dados.
E se você quiser uma versão mais avançada, também existe um filtro avançado no Excel.
Então, por que usar o AutoFiltro no VBA?
Se você só precisa filtrar dados e fazer algumas coisas básicas, eu recomendo manter a funcionalidade de Filtro embutida que a interface do Excel oferece.
Você deve usar o VBA Autofilter quando quiser filtrar os dados como parte de sua automação (ou se isso ajudar a economizar tempo, tornando a filtragem de dados mais rápida).
Por exemplo, suponha que você deseja filtrar rapidamente os dados com base em uma seleção suspensa e, em seguida, copiar esses dados filtrados em uma nova planilha.
Embora isso possa ser feito usando a funcionalidade de filtro embutida junto com um pouco de copiar e colar, pode levar muito tempo para fazer isso manualmente.
Nesse cenário, o uso do VBA Autofilter pode acelerar as coisas e economizar tempo.
Observação: Vou cobrir este exemplo (sobre como filtrar dados com base em uma seleção suspensa e copiá-los para uma nova planilha) posteriormente neste tutorial.
Sintaxe do filtro automático do Excel VBA
Expressão. AutoFiltro (_Field_, _Criteria1_, _Operator_, _Criteria2_, _VisibleDropDown_)
- Expressão: Este é o intervalo no qual você deseja aplicar o filtro automático.
- Campo: [Argumento opcional] Este é o número da coluna que você deseja filtrar. Isso é contado a partir da esquerda no conjunto de dados. Portanto, se você deseja filtrar os dados com base na segunda coluna, esse valor seria 2.
- Criteria1: [Argumento opcional] Este é o critério com base no qual você deseja filtrar o conjunto de dados.
- Operador: [Argumento opcional] Caso você também esteja usando o critério 2, pode combinar esses 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
- Criteria2: [Argumento opcional] Este é o segundo critério pelo qual você pode filtrar o conjunto de dados.
- VisibleDropDown: [Argumento opcional] Você pode especificar se deseja que o ícone suspenso do filtro apareça nas colunas filtradas ou não. Este argumento pode ser TRUE ou FALSE.
Além da Expressão, todos os outros argumentos são opcionais.
Caso você não use nenhum argumento, basta aplicar ou remover os ícones de filtro das colunas.
Sub FilterRows () Worksheets ("Filter Data"). Range ("A1"). AutoFilter End Sub
O código acima simplesmente aplicaria o método Autofilter às colunas (ou se 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, começará a vê-los quando o código acima for executado e, se puder vê-lo, 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 uso do Filtro automático VBA do Excel que tornarão seu uso mais claro.
Exemplo: Filtrando dados com base em uma condição de texto
Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja filtrá-lo com base na coluna ‘Item’.
O código a seguir filtraria todas as linhas em que o item é ‘Impressora’.
Sub FilterRows () Worksheets ("Sheet1"). Range ("A1"). Campo do AutoFilter: = 2, Criteria1: = "Printer" End Sub
O código acima se refere à Planilha1 e, dentro dela, refere-se a A1 (que é uma célula no conjunto de dados).
Observe que aqui usamos Field: = 2, pois a coluna do item é a segunda coluna em nosso conjunto de dados a partir da esquerda.
Agora, se você está pensando - por que preciso fazer isso usando um código VBA. Isso pode ser feito facilmente usando a funcionalidade de filtro embutida.
Você tem razão!
Se isso é tudo que você deseja fazer, é melhor usar a funcionalidade de filtro embutida.
Mas ao ler o tutorial restante, você verá que isso pode ser combinado com algum código extra para criar uma automação poderosa.
Mas antes de mostrá-los, deixe-me cobrir alguns exemplos para mostrar o que todos os métodos de AutoFiltro podem fazer.
Clique aqui para baixar o arquivo de exemplo e seguir adiante.
Exemplo: vários critérios (E / OU) na mesma coluna
Suponha que eu tenha o mesmo conjunto de dados e, desta vez, desejo filtrar todos os registros em que o item é ‘Impressora’ ou ‘Projetor’.
O código abaixo faria isso:
Sub FilterRowsOR () Worksheets ("Sheet1"). Range ("A1"). Campo do AutoFilter: = 2, Criteria1: = "Printer", Operator: = xlOr, Criteria2: = "Projector" End Sub
Observe que aqui eu usei o xlOR operador.
Isso informa ao VBA para usar os critérios e filtrar os dados se qualquer um dos dois critérios for atendido.
Da mesma forma, você também pode usar os critérios AND.
Por exemplo, se você deseja filtrar todos os registros em que a quantidade é maior que 10, mas menor que 20, você pode usar o código a seguir:
Sub FilterRowsAND () Worksheets ("Sheet1"). Range ("A1"). Campo do AutoFilter: = 4, Criteria1: = "> 10", _ Operator: = xlAnd, Criteria2: = "<20" End Sub
Exemplo: vários critérios com colunas diferentes
Suponha que você tenha o seguinte conjunto de dados.
Com o Autofilter, você pode filtrar várias colunas ao mesmo tempo.
Por exemplo, se você deseja filtrar todos os registros em que o item é ‘Impressora’ e o Representante de Vendas é ‘Marca’, você pode usar o código a seguir:
Sub FilterRows () With Worksheets ("Sheet1"). Range ("A1") .AutoFilter field: = 2, Criteria1: = "Printer" .AutoFilter field: = 3, Criteria1: = "Mark" End With End Sub
Exemplo: filtrar os 10 principais registros usando o método de filtro automático
Suponha que você tenha o conjunto de dados abaixo.
Abaixo está o código que fornecerá os 10 principais registros (com base na coluna de quantidade):
Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). Campo de AutoFiltro: = 4, Criteria1: = "10", Operador: = xlTop10Items End Sub
No código acima, usei o ActiveSheet. Você pode usar o nome da folha se quiser.
Observe que, neste exemplo, se você deseja obter os 5 itens principais, basta alterar o número em Critério1: = ”10 ″ de 10 a 5.
Portanto, para os 5 itens principais, o código seria:
Sub FilterRowsTop5 () ActiveSheet.Range ("A1"). Campo de AutoFiltro: = 4, Criteria1: = "5", Operador: = xlTop10Items End Sub
Pode parecer estranho, mas não importa quantos itens principais você queira, o valor Operator sempre permanece xlTop10Items.
Da mesma forma, o código abaixo daria a você os 10 itens inferiores:
Sub FilterRowsBottom10 () ActiveSheet.Range ("A1"). Campo de AutoFiltro: = 4, Criteria1: = "10", Operador: = xlBottom10Items End Sub
E se você quiser os últimos 5 itens, mude o número em Critério1: = ”10 ″ de 10 a 5.
Exemplo: filtrar os 10 por cento principais usando o método de filtro automático
Suponha que você tenha o mesmo conjunto de dados (conforme usado nos exemplos anteriores).
Abaixo está o código que fornecerá os principais registros de 10 por cento (com base na coluna de quantidade):
Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). Campo AutoFilter: = 4, Criteria1: = "10", Operador: = xlTop10Percent End Sub
Em nosso conjunto de dados, como temos 20 registros, ele retornará os 2 primeiros registros (o que é 10% do total de registros).
Exemplo: usando caracteres curinga no filtro automático
Suponha que você tenha um conjunto de dados conforme mostrado abaixo:
Se você deseja filtrar todas as linhas em que o nome do item contém a palavra ‘Tabuleiro’, você pode usar o código a seguir:
Sub FilterRowsWildcard () Worksheets ("Sheet1"). Range ("A1"). Campo do AutoFilter: = 2, Criteria1: = "* Board *" End Sub
No código acima, usei o caractere curinga * (asterisco) antes e depois da palavra 'Quadro' (que é o critério).
Um asterisco pode representar qualquer número de caracteres. Portanto, isso filtraria qualquer item que contenha a palavra "tabuleiro".
Exemplo: copiar linhas filtradas em uma nova planilha
Se você deseja não apenas filtrar os registros com base em critérios, mas também copiar as linhas filtradas, você pode usar a macro abaixo.
Ele copia as linhas filtradas, adiciona uma nova planilha e, a seguir, cola essas linhas copiadas na nova planilha.
Sub CopyFilteredRows () Dim rng As Range Dim ws As Worksheets ("Sheet1"). AutoFilterMode = False Then MsgBox "Não há linhas filtradas" Exit Sub End If Set rng = Worksheets ("Sheet1"). AutoFilter.Range Set ws = Worksheets.Add rng.Copy Range ("A1") End Sub
O código acima verificaria se há alguma linha filtrada na Planilha1 ou não.
Se não houver linhas filtradas, será exibida uma caixa de mensagem informando isso.
E se houver linhas filtradas, ele as copiará, inserirá uma nova planilha e colará essas linhas na planilha recém-inserida.
Exemplo: filtrar dados com base em um valor de célula
Usando o Autofilter no VBA junto com uma lista suspensa, você pode criar uma funcionalidade em que, assim que você selecionar um item da lista suspensa, todos os registros desse item serão filtrados.
Algo conforme mostrado abaixo:
Clique aqui para baixar o arquivo de exemplo e seguir adiante.
Esse tipo de construção pode ser útil quando você deseja filtrar dados rapidamente e usá-los posteriormente em seu trabalho.
Abaixo está o código que fará isso:
Sub Worksheet_Change privado (ByVal Target As Range) If Target.Address = "$ B $ 2" Then If Range ("B2") = "All" Then Range ("A5"). AutoFilter Else Range ("A5"). Campo AutoFilter : = 2, Criteria1: = Range ("B2") End If End If End Sub
Este é um código de evento de planilha, que é executado apenas quando há uma alteração na planilha e a célula de destino é B2 (onde temos o menu suspenso).
Além disso, uma condição If Then Else é usada para verificar se o usuário selecionou 'Todos' no menu suspenso. 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 contém esses dados.
Aqui estão as etapas para colocar este código na janela de código da planilha:
- Abra o Editor VB (atalho de teclado - ALT + F11).
- No painel Project Explorer, clique duas vezes no nome da planilha na qual deseja esta funcionalidade de filtragem.
- Na janela de código da planilha, copie e cole o código acima.
- Feche o Editor VB.
Agora, ao usar a lista suspensa, ela filtrará automaticamente os dados.
Este é um código de evento de planilha, que é executado apenas quando há uma alteração na planilha e a célula de destino é B2 (onde temos o menu suspenso).
Além disso, uma condição If Then Else é usada para verificar se o usuário selecionou 'Todos' no menu suspenso. Se All for selecionado, todo o conjunto de dados é mostrado.
Ligue / desligue o AutoFiltro do Excel usando VBA
Ao aplicar o Autofilter a um intervalo de células, pode já haver alguns filtros no lugar.
Você pode usar o código abaixo para desligar quaisquer filtros automáticos pré-aplicados:
Sub TurnOFFAutoFilter () Worksheets ("Sheet1"). AutoFilterMode = False End Sub
Este código verifica as planilhas inteiras e remove todos os filtros que foram aplicados.
Se você não deseja desativar os filtros de toda a planilha, mas apenas de um conjunto de dados específico, use o código a seguir:
Sub TurnOFFAutoFilter () If Worksheets ("Sheet1"). Range ("A1"). AutoFilter Then Worksheets ("Sheet1"). Range ("A1"). AutoFilter End If End Sub
O código acima verifica se já existem filtros em vigor ou não.
Se os filtros já estiverem aplicados, ele os remove, do contrário, não faz nada.
Da mesma forma, se você deseja ativar o AutoFiltro, use o código a seguir:
Sub TurnOnAutoFilter () If Not Worksheets ("Sheet1"). Range ("A4"). AutoFilter Then Worksheets ("Sheet1"). Range ("A4"). AutoFilter End If End Sub
Verifique se o AutoFiltro já foi aplicado
Se você tiver uma planilha com vários conjuntos de dados e quiser ter certeza de que não há filtros em vigor, pode usar o código a seguir.
Sub CheckforFilters () If ActiveSheet.AutoFilterMode = True Then MsgBox "Já existem filtros em vigor" Else MsgBox "Não há filtros" End If End Sub
Este código usa uma função de caixa de mensagem que exibe uma mensagem "Já existem filtros em vigor" quando encontra filtros na planilha, caso contrário, mostra "Não há filtros".
Mostrar todos os dados
Se você tiver filtros aplicados ao conjunto de dados e quiser mostrar todos os dados, use o código a seguir:
Sub ShowAllData () If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End Sub
O código acima verifica se FilterMode é TRUE ou FALSE.
Se for verdade, significa que um filtro foi aplicado e usa o método ShowAllData para mostrar todos os dados.
Observe que isso não remove os filtros. Os ícones de filtro ainda estão disponíveis para serem usados.
Usando AutoFiltro em Folhas Protegidas
Por padrão, quando você protege uma página, os filtros não funcionam.
Caso já tenha filtros em vigor, você pode habilitar o AutoFiltro para garantir que funcione mesmo em folhas protegidas.
Para fazer isso, marque a opção Use Autofilter enquanto protege a folha.
Embora isso funcione quando você já tem filtros no lugar, caso tente adicionar Autofilters usando um código VBA, não funcionará.
Uma vez que a planilha está protegida, ela não permitiria que nenhuma macro fosse executada e fizesse alterações no Autofiltro.
Portanto, você precisa usar um código para proteger a planilha e certificar-se de que os filtros automáticos estejam habilitados nela.
Isso pode ser útil quando você criou um filtro dinâmico (algo que cobri no exemplo - ‘Filtrar dados com base em um valor de célula’).
Abaixo está o código que protegerá a planilha, mas, ao mesmo tempo, permitirá que você use Filtros e também macros VBA nela.
Private Sub Workbook_Open () With Worksheets ("Sheet1") .EnableAutoFilter = True .Protect Password: = "password", Contents: = True, UserInterfaceOnly: = True End With End Sub
Este código precisa ser colocado na janela de código ThisWorkbook.
Aqui estão as etapas para colocar o código na janela de código ThisWorkbook:
- Abra o Editor VB (atalho de teclado - ALT + F11).
- No painel Project Explorer, clique duas vezes no objeto ThisWorkbook.
- Na janela de código que é aberta, copie e cole o código acima.
Assim que você abrir a pasta de trabalho e ativar as macros, ela executará a macro automaticamente e protegerá a Planilha1.
No entanto, antes de fazer isso, ele especificará ‘EnableAutoFilter = True’, o que significa que os filtros também funcionarão na folha protegida.
Além disso, ele define o argumento ‘UserInterfaceOnly’ como ‘True’. Isso significa que, enquanto a planilha estiver protegida, o código de macros VBA continuará a funcionar.
Você também pode gostar dos seguintes tutoriais de VBA:
- Loops do Excel VBA.
- Filtrar células com formatação de fonte em negrito.
- Gravando uma macro.
- Classificar dados usando VBA.
- Classifique as guias da planilha no Excel.
