none
Uso do Filtro na Tabela com VBA, os dados Some..... RRS feed

  • Pergunta

  • Bom dia.

    Pretendo usar uma Tabela dinamica para controlar os dados de uma planilha, e o motivo disso, e que achei que seria mais facil para quando acrescentar dados, as formatacoes e formulas sao copiadas automaticamente no registros adicionais.

    Tenho duas planilhas,msl1 e msl2 como mostra a Fig 1, sendo que a Tabela de nome tabela1 esta na planilha msl1. A intencao do filtro eh para quando tiver a letra "d" na coluna D o registro seria filtrado e copiado acumulativamente para a planilha msl2 e depois deletado da planilha msl1 como mostra a Fig 2.

    O codigo que fiz esta na Fig 3 e esta funcionando perfeitamente, so que quando nao tem nada na coluna D, o filtro evidentemente nao filtra nada mas os dados sao todos tranferidos na planilha msl2 e na planilha msl1 some tudo.

    Sera que tem alguma forma de contornar a situacao para que quando o operador apertar o botao por descuido e rodar a rotina e nao sumir os dados da planilha msl1? Se tiver um codigo que detecte que nao tem dados na coluna D daria para fazer um bay pass com If End If, mas nao encontrei o codigo que indique a coluna da Tabela que deve ser de referencia estruturada.

    Desde ja agradeco.

    Tadao

    Fig 1


    Fig 2


    Fig 3

    Sub Filtro()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    
    Set ws1 = ActiveWorkbook.Worksheets("msl1")
    Set ws2 = ActiveWorkbook.Worksheets("msl2")
    
    Application.DisplayAlerts = False
    
    With ws1.ListObjects("tabela1")
        .ShowAutoFilterDropDown = True
        .Range.AutoFilter field:=4, Criteria1:="D"
        .DataBodyRange.Copy ws2.Range("a" & Rows.Count).End(xlUp).Offset(1)
        .DataBodyRange.Delete
    End With
    
    ws1.Range("tabela1").AutoFilter field:=4
    ws1.ListObjects("tabela1").ShowAutoFilterDropDown = False
    
    Application.DisplayAlerts = True
    
    End Sub


    sexta-feira, 23 de outubro de 2015 11:44

Respostas

  • Antônio, a forma mais sucinta para obter uma coluna numa tabela é essa que mostrei.

    No entanto, você pode usar uma forma mais estruturada de tabela e acessar o intervalo de uma coluna com mais confiabilidade, usando Tabelas (ListObjects), que está na guia Página Inicial >> Formatar como Tabela. O código para acessar uma coluna é:

    Sub Main()
        Dim loData As ListObject
        Dim rColumn As Range
        
        Set loData = ThisWorkbook.Worksheets("Planilha1").ListObjects("Tabela1")
        Set rColumn = loData.ListColumns(4).DataBodyRange
    End Sub


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    terça-feira, 8 de dezembro de 2015 16:16
    Moderador

Todas as Respostas

  • Resumindo: a macro irá pular as linhas estão em brnaco na coluna D?

    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    sexta-feira, 23 de outubro de 2015 12:56
    Moderador
  • Na verdade, na coluna D esta preenchido com A  ou D sendo que o A seria os ativos e o D de desligado.
    sexta-feira, 23 de outubro de 2015 13:25
  • Algo como isso?

        If WorksheetFunction.CountIf(ws1.Columns("D"), "D") > 0 Then
            MsgBox "Não há dados para filtrar!", vbExclamation
            Exit Sub
        End If
    


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    sábado, 24 de outubro de 2015 20:37
    Moderador
  • Obrigado pela ajuda Felipe.

    Coloquei a sua sugestao na rotina como abaixo.

    Caso tenha algum impecilho na columa D fora da Tabela ira detectar a existencia de dados tambem.Tentei delimitar na coluna dentro da Tabela usando CountIf(.ListColumns(4),"D") (Como mostra comentado abaixo) mas nao tive sucesso. Teria uma forma de contar so dentro da coluna da Tabela?. Sera que teria que usar um For Each ? como seria o codigo?

    Tadao

    Sub Filtro()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    
    Set ws1 = ActiveWorkbook.Worksheets("msl1")
    Set ws2 = ActiveWorkbook.Worksheets("msl2")
    
    Application.DisplayAlerts = False
    
    If WorksheetFunction.CountIf(ws1.Columns("D"), "D") > 0 Then
    
        With ws1.ListObjects("tabela1")
            .ShowAutoFilterDropDown = True
    '        If WorksheetFunction.CountIf(.ListColumns(4), "D") > 0 Then
                .Range.AutoFilter field:=4, Criteria1:="D"
                .DataBodyRange.Copy ws2.Range("a" & Rows.Count).End(xlUp).Offset(1)
                .DataBodyRange.Delete
    '        End If
            
        End With
    End If
    
    ws1.Range("tabela1").AutoFilter field:=4
    ws1.ListObjects("tabela1").ShowAutoFilterDropDown = False
    
    Application.DisplayAlerts = True
    
    End Sub
    

    domingo, 25 de outubro de 2015 03:34
  • Gostaria de expor melhor a minha duvida.

    Na figura abaixo onde esta ecurecido e a area da Tabela onde aplico o filtro.

    Aplicando o filtro com o codigo sugerido funciona bem como abaixo indicado

    Mas quando tem um impecilho fora da tabela conforme mostra a celula com fundo vermelho, e aplico o filtro.....

    ....os dados some, ou seja sao todos transferidos.

    Em vez de usar o

    WorksheetFunction.CountIf(ws1.Columns("D"), "D")

    Teria uma forma de contar so dentro da coluna da Tabela?

    Caso nao tenha outra forma, a solucao e tomar cuidado para nao ter dados indesejados, nao seria?

    Tadao

    domingo, 6 de dezembro de 2015 02:44
  • WorksheetFunction.CountIf(Intersect(ws1.Columns("D"), ws1.Range("D2").CurrentRegion), "D")


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    segunda-feira, 7 de dezembro de 2015 12:12
    Moderador
  • Obrigado pelo retorno.

    Fiz as modificacoes e funciona bem quando tem espaco entre a Tabela e o impecilho. Acho que e devido ao recurso que foi usado do CurrentRegion que detecta como sendo da mesma regiao quando o impecilho nao tem espaco separando com a Tabela.

    Pelo que estou percebendo, nao tem um codigo que diz para contar a coluna D da Tabela, nao e?, tem usar de recursos e mix de outros codigos. Nao sei como montar o codigo, mas nao teria um jeito de contar as linhas da interseccao da coluna D com a linhas da Tabela?

    Mas a solucao exposta ja foi de grande ajuda e agradeco muito. Obrigado.

    Tadao

    terça-feira, 8 de dezembro de 2015 06:02
  • Antônio, a forma mais sucinta para obter uma coluna numa tabela é essa que mostrei.

    No entanto, você pode usar uma forma mais estruturada de tabela e acessar o intervalo de uma coluna com mais confiabilidade, usando Tabelas (ListObjects), que está na guia Página Inicial >> Formatar como Tabela. O código para acessar uma coluna é:

    Sub Main()
        Dim loData As ListObject
        Dim rColumn As Range
        
        Set loData = ThisWorkbook.Worksheets("Planilha1").ListObjects("Tabela1")
        Set rColumn = loData.ListColumns(4).DataBodyRange
    End Sub


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    terça-feira, 8 de dezembro de 2015 16:16
    Moderador
  • Acho que fiz uma falta na segunda postagem quando disse que nao tive sucesso quando coloquei o codigo como abaixo...

    Tentei delimitar na coluna dentro da Tabela usando CountIf(.ListColumns(4),"D") (Como mostra comentado abaixo) mas nao tive sucesso. Teria uma forma de contar so dentro da coluna da Tabela?

    '        If WorksheetFunction.CountIf(.ListColumns(4), "D") > 0 Then
                .Range.AutoFilter field:=4, Criteria1:="D"
                .DataBodyRange.Copy ws2.Range("a" & Rows.Count).End(xlUp).Offset(1)
                .DataBodyRange.Delete
    '        End If

    .......depois que vi a sua ultima postagemnotei que apos o ListColumns(4) faltou acressentar o DataBodyRange por isso dava erro..........

    Ja estava desistindo e conformado,....mas a salvacao veio no ultimo Gongo......Obrigado Felipe....pequenos detalhes, grandes solucoes.....

    Se pudesse me ensinar so mais uma Dica.......como vou saber se para identificar a coluna dentro da Tabela tem que colocar o DataBodyRange?

    O codigo ficou como imaginado e anexo abaixo.

    Sub filtro()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    
    Set ws1 = ActiveWorkbook.Worksheets("msl1")
    Set ws2 = ActiveWorkbook.Worksheets("msl2")
    
    Application.DisplayAlerts = False
    
        With ws1.ListObjects("tabela1")
        
        'verifica se nao tem nemhuma letra "D" na coluna(4) da Tabela
    
        If WorksheetFunction.CountIf(.ListColumns(4).DataBodyRange, "D") > 0 Then
    
                .ShowAutoFilterDropDown = True
                
                    .Range.AutoFilter field:=4, Criteria1:="D"
                    .DataBodyRange.Copy ws2.Range("a" & Rows.Count).End(xlUp).Offset(1)
                    .DataBodyRange.Delete
                
        End If
        
        End With
    
    ws1.Range("tabela1").AutoFilter field:=4
    ws1.ListObjects("tabela1").ShowAutoFilterDropDown = False
    
    Application.DisplayAlerts = True
    
    End Sub


    quinta-feira, 10 de dezembro de 2015 12:55
  • Não sei se entendi muito bem o que perguntou, mas .ListColumns(4) retorna um tipo de dados que não é um Range, e você só consegue procurar valores em células se for num intervalo Range, daí a necessidade de se colocar sempre o DataBodyRange.

    Se quiser considerar o cabeçalho da ListColumn além dos dados, Use .Range ao invés de DataBodyRange.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    sexta-feira, 11 de dezembro de 2015 12:28
    Moderador
  • Obrigado pelo esclarecimento Felipe, entao se nao colocar o DataBodyRange nao e Range no caso da Tabela?

    Eu nao tinha colocado (alias nao sabia que existia isso) nada depois do ListColumns(4) por analogia pois quando e na planilha usa o CountIf(ws1.Columns(4),"D") sem nenhum complemento, por isso perguntei a Dica onde saber o que complementar.......

    Depois de algumas verificacoes observei que tem outra forma de referir-se a determinada coluna da Tabela tabela1  coluna Cab.4 como Countif(Range("tabela1[Cab.4]","D")

    Tenho ainda muito que aprender....chego la....

    Mais uma vez agradeco pela atencao.

    Tadao



    sábado, 12 de dezembro de 2015 08:37
  • "Eu nao tinha colocado (alias nao sabia que existia isso) nada depois do ListColumns(4) por analogia pois quando e na planilha usa o CountIf(ws1.Columns(4),"D") sem nenhum complemento, por isso perguntei a Dica onde saber o que complementar......."

    Antônio, o método WorksheetFunction.CountIf necessita de dois parâmetros. O primeiro, de nosso interesse, deve ser do tipo de dados Range, como pode-se ver pela dica ao abrir o parênteses digitando a função.

    Se você fizer um teste na janela de verificação imediata, em modo de depuração, digitando ?TypeName(ws1.Columns(4)), verá que o tipo de dados dessa expressão é um Range. Ok, é exatamente o que a função precisa.

    Por outro lado, se você fizer ?TypeName(.ListColumns(4)), verá que obterá o tipo de dados ListColumn, justificando o erro. Já ?TypeName(.ListColumns(4).DataBodyRange) retorna um tipo de dados Range, que é o que o método precisa.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    sábado, 12 de dezembro de 2015 19:18
    Moderador
  • Acho que preciso fazer mais uso dessa verificacao imediata.Foi como voce descreveu.

    Verifiquei que depois de colocar o ponto apos Columns(4), aparece uma lista de codigos a serem complementados e dentre eles estava o DataBodyRange..............para iniciante como eu, e meio dificil interpretar os complementos,nao?.....mas se o tempo permitir....chego la. Mais uma vez obrigado.

    Tadao

    domingo, 13 de dezembro de 2015 16:01