✔ Programação GLOBAL®: VBA Excel - Interagindo com as Funções AutoFiltro

DESENVOLVO APLICAÇÕES VBA

Acesse uma ENORME lista de Dashboards e Infográficos

Aprenda a detectar gráficos que precisam mudar

Dados ou Informações?

Dashboards - 8 Importantes características desejáveis

Dashboards e Infocharts - Diversas Visualizações de Dados

Diversos exemplos de Gráficos

Excel - DashMap - Mapa do Brasil

Key Performance Indicators - 7 Características desejáveis

MIS - Usufruindo a excelência na análise das suas Informações

O Melhor do Business Intelligence

Os 10 mandamentos do gerenciamento de projetos

Receba 3 Calendários Customizáveis de 2013 em Excel

Usando Gauges em Dashboards no MS Access

Você tem direito as informações da sua empresa

Seguem alguns exemplos de programação VBA para manipular o AutoFiltro do MS Excel, para uso com as listas de dados encontrados nas tabelas das nossas planilhas.

Quando nomeamos estas tabelas de dados constituimos um ListObject, o qual automaticamente recebe a sua própria propriedade AutoFiltro.

Mostrando todos os registros

O código abaixo mostra todos os registros duma lista na planilha ativa, onde um filtro foi aplicado.

Sub ShowAllRecordsList1()

' Mostra todos os registros

Dim Lst As ListObject

Set Lst = ActiveSheet.ListObjects(1)

If Lst.AutoFilter.FilterMode Then

Lst.AutoFilter.ShowAllData

End If

End Sub

Ligando o AutoFiltro da nossa Lista

Ao usar o código a seguir, você ligará o AutoFiltro do Excel na 1ª lista da planilha ativa.

Sub TurnAutoFilterOnList1()

' Liga o AutoFiltro na 1ª lista.

Dim Lst As ListObject

Set Lst = ActiveSheet.ListObjects (1)

Let Lst.ShowAutoFilter = True

End Sub

Desligando a Lista de AutoFiltro

Utilize o código a seguir para desligar um AutoFiltro do Excel na 1ª lista da planilha ativa.

Sub TurnAutoFilterOffList1()

' Desliga o AutoFiltro na 1ª lista.

Dim Lst As ListObject

Set Lst = ActiveSheet.ListObjects(1)

Let Lst.ShowAutoFilter = False

End Sub

Contando as Listas de AutoFiltros

Para contar todas as listas e tabelas nomeadas duma planilha, onde existem AutoFiltros ativos, usamos o código a seguir.

Sub CountListAutoFilters()

' Conta a Lista de AutoFiltros mesmo que todas as seta fiquem escondidas.

Dim Lst As ListObject

Dim i As Long

Let i = 0

For Each Lst In ActiveSheet.ListObjects

If Lst.ShowAutoFilter = True Then

Let i = i + 1

End If

Next Lst

Debug.Print "Lista de autofiltros: " & i

End Sub

Ocultando todas as Setas da lista de AutoFiltro, exceto uma

Talvez deseje que os seus usuários filtrem apenas uma das colunas da sua primeira Lista. O procedimento VBA a seguir, esconde as setas de todas as colunas, exceto a segunda coluna da 1ª lista.

Sub HideArrowsList1()

'hides all arrows except list 1 column 2

Dim Lst As ListObject

Dim c As Range

Dim i As Integer

Let Application.ScreenUpdating = False

Set Lst = ActiveSheet.ListObjects(1)

Let i = 1

For Each c In Lst.HeaderRowRange

If i <> 2 Then

Lst.Range.AutoFilter Field:=i, _

VisibleDropDown:=False

Else

Lst.Range.AutoFilter Field:=i, _

VisibleDropDown:=True

End If

Let i = i + 1

Next

Let Application.ScreenUpdating = True

End Sub

Ocultando Setas específicas nas listas de AutoFiltro

Talvez, em alguns casos específicos, queiramos ocultar as setas de colunas específicas na nossa lista de dados, deixando as demais setas visíveis. O código a seguir esconde as setas das colunas 1, 3 e 4 na 2ª lista.

Sub HideSpecifiedArrowsList2()

' Esconde setas (arrows) em colunas específicas na 2ª lista.

Dim Lst As ListObject

Dim c As Range

Dim i As Integer

Let Application.ScreenUpdating = False

Set Lst = ActiveSheet.ListObjects(2)

Let i = 1

For Each c In Lst.HeaderRowRange

Select Case i

Case 1, 3, 4

Lst.Range.AutoFilter Field:=i, _

Visibledropdown:=False

Case Else

Lst.Range.AutoFilter Field:=i, _

Visibledropdown:=True

End Select

Let i = i + 1

Next

Let Application.ScreenUpdating = True

EndSub

Visualizar todas as setas da Lista AutoFiltro

Para mostrar todas as setas da 1ª Lista, podemos usar o código a seguir:

Sub ShowArrowsList1()

Dim Lst As ListObject

Dim c As Range

Dim i As Integer

Let Application.ScreenUpdating = False

Set Lst = ActiveSheet.ListObjects(1)

Let i = 1

For Each c In Lst.HeaderRowRange

Lst.Range.AutoFilter Field:=i, _

Visibledropdown:=True

Let i = i + 1

Next

Let Application.ScreenUpdating = True

End Sub

Copiando Linhas filtradas específicas, sem os títulos

Este simples código copia somente as linhas filtradas, mas não os respectivos títulos.

A cópia é feita a partir da 1ª Lista na planilha ativa, para uma nova planilha.

Sub CopyFilteredRowsOnlyList1()

Dim wsL As Worksheet

Dim ws As Worksheet

Dim rng As Range

Dim rng2 As Range

Dim Lst As ListObject

Let Application.ScreenUpdating = False

Set wsL = ActiveSheet

Set Lst = wsL.ListObjects(1)

With Lst.AutoFilter.Range

On Error Resume Next

Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _

.SpecialCells(xlCellTypeVisible)

On Error GoTo 0

End With

If rng2 Is Nothing Then

MsgBox "Sem dados para copiar."

Else

Set ws = Sheets.Add

Set rng = Lst.AutoFilter.Range

' Copia todas as linhas sem os cabeçalhos

rng.Offset(1, 0).Resize(rng.Rows.Count - 1) _

.SpecialCells(xlCellTypeVisible).Copy _

Destination:=ws.Range("A1")

End If

Let Application.ScreenUpdating = True

End Sub

Copiando Linhas filtradas específicas, com os títulos

O código abaixo copia as linhas filtradas, e as suas respectivas posições, com os títulos.

Sub CopyFilteredRowsAndHeadingsList1()

Dim wsL As Worksheet

Dim ws As Worksheet

Dim rng As Range

Dim rng2 As Range

Dim Lst As ListObject

Let Application.ScreenUpdating = False

Set wsL = ActiveSheet

Set Lst = wsL.ListObjects(1)

With Lst.AutoFilter.Range

On Error Resume Next

Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _

.SpecialCells(xlCellTypeVisible)

On Error GoTo 0

End With

If rng2 Is Nothing Then

MsgBox "Sem dados para copiar."

Else

Set ws = Sheets.Add

Set rng = Lst.AutoFilter.Range

' Copia as Linhas com os seus cabeçalhos.

rng.SpecialCells(xlCellTypeVisible).Copy _

Destination:=ws.Range("A1")

End If

Let Application.ScreenUpdating = True

End Sub

Conte as Linhas da Lista Visível

Com o exemplo do código a seguir, exibiremos uma mensagem que mostra quantas linhas estão visíveis após a aplicação do filtro:

Sub CountVisibleRowsList1()

Dim Lst As ListObject

Dim rng As Range

Set Lst = ActiveSheet.ListObjects(1)

Set rng = Lst.AutoFilter.Range

MsgBox rng.Columns(1). _

SpecialCells(xlCellTypeVisible).Count - 1 _

& " of " & rng _

.Rows.Count - 1 & " Registros"