Excel VBA ComboBox exibindo resultados de um filtro

[4tomic]

Comunidade, boa tarde!

 

Estou iniciando no mundo do VBA e tenho desenvolvido algumas soluções para otimização de minhas rotinas no trabalho, porém estou com uma dificuldade em uma solução, cuja qual eu encontrei porém o código esta pesado e leva tempo até processá-lo, peço então, o apoio de vocês no sentido de uma otimização do meu código, ou de um código novo que possa fazer tal função.

 

Tenho um formulário, neste há uma combobox cuja qual eu quero que exiba os valores resultantes de um filtro aplicado a uma planilha que está nesta mesma pasta de trabalho. Consegui fazer através da aplicação do filtro e checagem das células vísiveis. O problema é que a planilha onde é feito o filtro é longa e tem quase 6000 linhas, sendo assim, o código que desenvolvi demora a exibir os resultados na combobox, segue este abaixo. Há alguma solução 

 

PS: não fiz por cells.find porque as vezes preciso de 2 variáveis de busca para chegar a um resultado um pouco mais preciso.

 

Sub Carrega_ComboBox1()
Dim Cont_limp As Integer
Cont_limp = 1
Planilha3.Activate
Selection.CurrentRegion.Select
    Selection.AutoFilter
    
   
    ActiveSheet.Range("$A$5:$L$6169").AutoFilter Field:=8, Criteria1:= _
        "=*limpeza*", Operator:=xlAnd, Criteria2:="=*terreno*"

Range("H5").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(Cont_limp, 0).Select
    If ActiveCell.EntireRow.Hidden = False Then
    UserForm1.ComboBox1.AddItem ActiveCell.Value
    End If
Loop
End Sub

 

Desde já agradeço

 

 

 

 

[Visitante]

2 horas atrás, 4tomic disse:

... porém o código esta pesado e leva tempo até processá-lo ...

... peço então, o apoio de vocês no sentido de uma otimização do meu código, ou de um código novo que possa fazer tal função.

 

Causas prováveis da lentidão:

1. o uso do comando Select (não utilize, a não ser que seja necessário/conveniente, o que é raro)

2. o Loop analisa todas as linhas preenchidas da coluna H (se, por exemplo, na planilha houver 6.000 registros e o filtro exibir somente 400 registros, o Loop irá analisar 5.600 registros sem necessidade de fazê-lo)

 

Segue abaixo um código equivalente ao seu, sem Select e que faz o Loop somente nas células filtradas.

Sub Carrega_ComboBox1()
 Dim CélVis As Range
  With ActiveSheet
   .AutoFilterMode = False
   .Range("A5:L" & .Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=8, Criteria1:= _
    "=*limpeza*", Operator:=xlAnd, Criteria2:="=*terreno*"
     For Each CélVis In .Range("H5:H" & .Cells(Rows.Count, "H").End(xlUp).Row).SpecialCells(xlVisible)
      UserForm1.ComboBox1.AddItem CélVis.Value
     Next CélVis
   .AutoFilterMode = False
  End With
End Sub

 

Abaixo uma outra abordagem para carregar a Combo via o método List no lugar do método AddItem.

O código irá replicar a lista filtrada na Plan1 e de lá irá carregar a Combo. Provavelmente este é mais rápido.

Sub Carrega_ComboBox1()
 Sheets("Plan1").Columns(1).Value = ""
  With ActiveSheet
   .AutoFilterMode = False
   .Range("A5:L" & .Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=8, Criteria1:= _
    "=*limpeza*", Operator:=xlAnd, Criteria2:="=*terreno*"
   .Range("H5:H" & .Cells(Rows.Count, "H").End(xlUp).Row).Copy Sheets("Plan1").[A1]
     Me.ComboBox1.List = Sheets("Plan1").Range("A1:A" & Sheets("Plan1").Cells(Rows.Count, 1).End(xlUp).Row).Value
   .AutoFilterMode = False
  End With
End Sub

obs.

1. considerei que a planilha ativa será a Planilha3 e que haverá a Plan1 ao rodar o código

2. considerei que sempre haverá células filtradas na coluna H

3. se você tiver dificuldade então disponibilize o seu arquivo

[4tomic]

15 horas atrás, osvaldomp disse:

 

Causas prováveis da lentidão:

1. o uso do comando Select (não utilize, a não ser que seja necessário/conveniente, o que é raro)

2. o Loop analisa todas as linhas preenchidas da coluna H (se, por exemplo, na planilha houver 6.000 registros e o filtro exibir somente 400 registros, o Loop irá analisar 5.600 registros sem necessidade de fazê-lo)

 

Segue abaixo um código equivalente ao seu, sem Select e que faz o Loop somente nas células filtradas.

Sub Carrega_ComboBox1()
 Dim CélVis As Range
  With ActiveSheet
   .AutoFilterMode = False
   .Range("A5:L" & .Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=8, Criteria1:= _
    "=*limpeza*", Operator:=xlAnd, Criteria2:="=*terreno*"
     For Each CélVis In .Range("H5:H" & .Cells(Rows.Count, "H").End(xlUp).Row).SpecialCells(xlVisible)
      UserForm1.ComboBox1.AddItem CélVis.Value
     Next CélVis
   .AutoFilterMode = False
  End With
End Sub

 

Abaixo uma outra abordagem para carregar a Combo via o método List no lugar do método AddItem.

O código irá replicar a lista filtrada na Plan1 e de lá irá carregar a Combo. Provavelmente este é mais rápido.

Sub Carrega_ComboBox1()
 Sheets("Plan1").Columns(1).Value = ""
  With ActiveSheet
   .AutoFilterMode = False
   .Range("A5:L" & .Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=8, Criteria1:= _
    "=*limpeza*", Operator:=xlAnd, Criteria2:="=*terreno*"
   .Range("H5:H" & .Cells(Rows.Count, "H").End(xlUp).Row).Copy Sheets("Plan1").[A1]
     Me.ComboBox1.List = Sheets("Plan1").Range("A1:A" & Sheets("Plan1").Cells(Rows.Count, 1).End(xlUp).Row).Value
   .AutoFilterMode = False
  End With
End Sub

obs.

1. considerei que a planilha ativa será a Planilha3 e que haverá a Plan1 ao rodar o código

2. considerei que sempre haverá células filtradas na coluna H

3. se você tiver dificuldade então disponibilize o seu arquivo

 

Osvaldo, boa tarde!

 

Gostaria de te agradecer pelo apoio. Vou testar o código, pelo que vi acredito que ele dará certo sim, tentarei com o primeiro código pois terei muitas dessa combobox, e se eu colar a filtragem e outra planilha, não sei até que ponto será viável.

 

Voltarei aqui para te dar o feedback quanto a solução.

 

 

[Visitante]

1 hora atrás, 4tomic disse:

 ... e se eu colar a filtragem e outra planilha, não sei até que ponto será viável.

Você não vai colar nada.

Conforme comentei no post anterior o código que passei fará essa operação.

Se eu não tivesse comentado provavelmente você nem perceberia.

 

[4tomic]

Eu percebi, o problema é que poderei ter mais de 100 dessas combobox, e através do segundo código necessariamente precisaria da planilha 1 para colar os filtros. Testei o código 1 e ele funcionou perfeitamente, de forma MUUITO mais rápida que o meu código original e consegui aprender um pouco mais analisando seu código. Muito obrigado.

[Visitante]

2 horas atrás, 4tomic disse:

Testei o código 1 e ele funcionou perfeitamente, de forma MUUITO mais rápida que o meu código original ...

Que bom! Ficamos contentes em saber que você encontrou uma solução para a sua demanda.

 

... o problema é que poderei ter mais de 100 dessas combobox, e através do segundo código necessariamente precisaria da planilha 1 para colar os filtros.

Se entendi corretamente você afirma que a necessidade de uma planilha auxiliar com o fim de receber os registros filtrados é que seria um problema (ou acarretaria um problema), ao utilizar o segundo código, é isso ?

Se você puder nos informe qual exatamente seria esse problema, assim todos aprendemos.

 

 

[4tomic]

Osvaldo, na verdade não seria de fato um problema, é que eu aumentaria o tamanho do arquivo, nessa planilha eu terei pelo menos umas 100 dessa combobox fazendo filtros para retornar resultados que forem selecionados, se para cada combobox que eu criar eu colar o resultado dos filtros na planilha1, vejo que só estarei sobrecarregando o arquivo, pois seu primeiro código, faz a filtragem, retorna o resultado e desfaz o filtro, sendo assim em todas as combobox que eu utilizar ele me trará o resultado esperado sem precisar colar esse filtro em outro lugar.

[Visitante]

Entendi.

Mas só pra esclarecer (e finalizar), os dados filtrados que o código cola na planilha auxiliar correspondem a somente uma Combo, pois ao rodar o código, o primeiro comando dele já limpa os dados colados da Combo anterior, por isso os dados armazenados correspondem a somente uma Combo, independente de quantas forem processadas.

 

Pelo exposto, acredito que o acréscimo no tamanho do arquivo acarretado pela existência da planilha auxiliar é irrelevante, ainda, pode ser adicionado um comando para limpá-la ao encerrar o código, ainda, pode ser adicionado ao código um comando para excluí-la antes de salvar o arquivo.

 

Finalizando, para processar uma grande quantidade de Combos valeria fazer um teste com aquele código, que provavelmente rodará mais rápido do que o primeiro.

[4tomic]

Hmm, não tinha me atentado que o código limparia os dados da planilha 1, sendo assim, ele pode ser viável sim, ainda mais se você diz que ele é mais rápido, vou testá-lo então. Estou trabalhando nesta planilha ainda e há muito o que desenvolver, quando chegar próximo ao formato final testarei.

[Patropi]

@4tomic

 

O Osvaldo esta te ajudando e até agora você não clicou nem uma vez em curtir nas respostas dele.

 

[]s

[4tomic]

@Patropi

Na verdade, embora eu seja membro antigo, é a primeira vez que uso o forum. Não sabia desta regra de conduta. O Osvaldo me ajudou muito, com certeza darei os likes nos posts dele.

 

Abraços