Perguntas e Respostas #2 – Macro para ocultar linhas de acordo com critério definido pelo usuário

Olá Excelentes pessoas!!!

Hoje teremos mais um artigo da série Perguntas e Respostas de Excel. Ou seja, de todos os comentários que recebo aqui no site, por email ou redes sociais, eu seleciono alguns que podem transformar-se em um artigo bem interessante a todos.

Por isso, te convido a interagir conosco aqui no site. Comentem, sigam-nos nas redes sociais (já temos twitter e em breve criaremos um facebook para o site). E para ficar sabendo de todas as novidades cadastrem-se para recebê-las em seu email.

Bom. Então vamos a dica de hoje.

Comentário postado pelo leitor no artigo ensinando uma macro para descobrir a última linha e coluna escrita numa planilha.

Gostaria de saber como posso ocultar linhas que estão fora de um intervalo de datas.

Por exemplo:

Tenho duas células cada uma com uma data, A1: 03/04/2007 e B1: 02/10/2012.
Em outra planilha da mesma pasta possuo datas na coluna A10 até A250, iniciando em 01/01/2003 e terminando numa outra data qualquer, sendo que as duas datas acima estão no intervalo entre as linhas 10 e 250 da coluna “A”.
Pergunto: Como devo proceder para, automaticamente ou com fórmulas, ocultar e/ou excluir as datas anteriores a 03/04/2007 e posteriores a 02/10/2012?

Entendendo a questão

O leitor gostaria de, não simplesmente ocultar algumas linhas de sua planilha, mas de suprimir a exibição dessas linhas de maneira restritiva, ou seja, tomando-se um intervalo, excluir ou ocultar apenas as linhas que atendam a determinados critérios definidos previamente por ele.

Acredito que para um melhor entendimento de vocês, possamos comparar este processo com o que é obtido através do recurso AutoFiltro.

Neste exemplo específico citado na questão do leitor, poderíamos ocultar as linhas que não atendessem ao critério estabelecido pelo usuário, no caso, exibir apenas as linhas que tivessem, na coluna A, as datas entre 03/04/2007 e 02/10/2012. Isso é simples! Veja.

Para habilitar o Auto Filtro, faça como na figura abaixo ou tecle CTRL+SHIFT+L ou, ainda, clique em Filtro na aba Dados.

Depois clique na seta para filtrar a coluna A que contêm as datas. Em seguida clique em Filtros de Data > Está entre…

Agora, digite as datas que deseja filtrar.

Com isso, só serão exibidas as linhas onde as datas estiverem no período entre as duas datas informadas. Posteriormente, para limpar o filtro, clique novamente na setinha da coluna data e selecione a opção Limpar Filtro de “Data”.

 

Caso resolvido? Ainda não!

Dessa forma, o resultado foi alcançado, entretanto, o processo está sendo realizado de forma manual. E queremos, automatizar isso.

Então como fazer isso?

 

Apresentando uma solução

Como vimos, a solução acima não é tão automatizada assim. De acordo com o leitor, a intenção seria que toda essa filtragem seja realizada no momento em que as datas fossem digitadas na célula.

Para conseguirmos isso, podemos automatizar todo o processo realizado acima, no momento em que digitarmos as datas nas células específicas. Como?

Primeiramente, utilizaremos o evento disparado pela planilha quando modificamos o conteúdo de alguma célula. Este evento é encontrado no módulo de planilha e chama-se Worksheet_Change.

Vamos restringi-lo às alterações ocorridas nas células B5 e C5, de acordo com o exemplo que estou deixando para vocês fazerem download.

Acessem o ambiente de projeto do VBA (ALT+F11), abram o módulo da Plan1 e digitem o código abaixo.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Erro

    With Target
        If .Row = 5 Then
            If .Column = 2 Or .Column = 3 Then
                Call FiltrarPorData(.Worksheet.Cells(.Row, 2), .Worksheet.Cells(.Row, 3))
            End If
        End If
    End With

Exit Sub

Erro:
    MsgBox Err.Description, vbExclamation, "Desenvolvido por Excel do Seu Jeito"
    Exit Sub
End Sub

Percebam que neste trecho do código estamos fazendo uma chamada da rotina FiltrarPorData, que será responsável por executar o auto-filtro na planilha de dados.

A rotina que executa a filtragem automática ficará num Módulo global. Por isso, insira o Módulo1 ao projeto e escreve o código a seguir.

Sub FiltrarPorData(sData1 As String, sData2 As String)
Dim shFiltrar As Worksheet
Dim rngAll As Range

On Error GoTo Erro

    Set shFiltrar = ThisWorkbook.Sheets("Exemplo Auto-Filtro")
    Set rngAll = shFiltrar.Range("$A$1:$B$250")

    If IsDate(sData1) And IsDate(sData2) Then
        sData1 = Format(sData1, "mm/dd/yyyy")
        sData2 = Format(sData2, "mm/dd/yyyy")

        rngAll.AutoFilter Field:=1, Criteria1:= _
            ">=" & sData1, Operator:=xlAnd, Criteria2:="<=" & sData2

        shFiltrar.Activate
    Else
        rngAll.AutoFilter Field:=1
    End If

Exit Sub
Erro:
    MsgBox Err.Description, vbExclamation, "Desenvolvido por Excel do Seu Jeito"
    Exit Sub
End Sub

Está pronto. Agora quando digitar as datas na Plan1 o filtro será aplicado “automagicamente“.

Mas, ainda falta alguma coisa….

Ahhh!!! Sim… Nós estamos apenas ocultando as linha que não nos interessam, mas, elas ainda continuam lá. Para excluí-las, precisamos utilizar o código que mostro a seguir.

 

E se eu quiser excluir os resultados que não atendem aos critérios do filtro aplicado?

Então… Não vou complicar muito pra vocês não!!! Vamos aproveitar o que já temos até agora.

Já temos a automatização que oculta os resultados que não atendem aos critérios. Com isso, vamos fazer um loop percorrendo toda a lista e verificando as linhas que estão ocultas e excluindo-as. Simples né? Veja o código com essa alteração:

Sub FiltrarPorData(sData1 As String, sData2 As String)
Dim shFiltrar As Worksheet
Dim rngAll As Range
Dim rng As Range
Dim i As Integer

On Error GoTo Erro

    Set shFiltrar = ThisWorkbook.Sheets("Exemplo Auto-Filtro")
    Set rngAll = shFiltrar.Range("$A$1:$B$250")

    If IsDate(sData1) And IsDate(sData2) Then
        sData1 = Format(sData1, "mm/dd/yyyy")
        sData2 = Format(sData2, "mm/dd/yyyy")

        rngAll.AutoFilter Field:=1, Criteria1:= _
            "&gt=" & sData1, Operator:=xlAnd, Criteria2:="&lt=" & sData2

        If MsgBox("Deseja excluir os resultados que não correspondam aos critérios da pesquisa? Esses dados não poderão ser recuperados!", vbQuestion + vbDefaultButton2 + vbYesNo) = vbYes Then
            For i = rngAll.Rows.Count To 1 Step -1
                If shFiltrar.Rows(i).Hidden Then
                    shFiltrar.Rows(i).EntireRow.Delete
                End If
            Next i
        End If

        shFiltrar.Activate
    Else
        rngAll.AutoFilter Field:=1
    End If

Exit Sub
Erro:
    MsgBox Err.Description, vbExclamation, "Desenvolvido por Excel do Seu Jeito"
    Exit Sub
End Sub

 

Então é isso, pessoal. Espero que tenham gostado.

E para quem ainda não tem o e-Book Aprenda 7 Fórmulas do Excel que podem Salvar sua Vida, peça já o seu. Neste ebook, ensino como utilizar as principais fórmulas do Excel, ou seja, aquelas fórmulas que você precisa saber, pois elas sempre serão necessárias na maioria de suas planilhas. Disponibilizo vários exemplos práticos do dia a dia para você entender melhor o seu funcionamento e testar o que está aprendendo.

Um abração

1 comentário em “Perguntas e Respostas #2 – Macro para ocultar linhas de acordo com critério definido pelo usuário”

  1. Que maravilha ver um profissional deste nipe se doando com uma linguagem ultra didática, passo a passo, provendo um entendimento da forma que mais se espera efetivamente aprender. Rogo para que voces deste site sejam abençoados, pois, é assim que se deve ser, férteis espiritualmente e preocupados em melhorar os outros expondo vossos conhecimentos com arte.

    Parabéns.

    Responder

Deixe um comentário