Excel VBA – Excluir linhas de acordo com um critério

renaut77

02-09-2009 18:37:20

Cao Tomás,
Vi em um de seus artigos sobre o tema acima com a seguinte codificação:

Function ExcluiLinhasPorCriterio(ByVal linhaInicial As Integer, ByVal linhaFinal As Integer, ByVal colunaCriterio As Integer, ByVal criterio As String) As Long
Dim linhasExcluidas As Integer
Dim i As Integer
linhasExcluidas = 0
With ActiveSheet
i = linhaInicial
While i < linhaFinal
If CStr(.Cells(i, colunaCriterio).Value) <> criterio Then
.Rows(i).Delete
linhasExcluidas = linhasExcluidas + 1
Else
i = i + 1
End If
Wend
End With
ExcluiLinhasPorCriterio = linhasExcluidas
End Function

Sub Executa()
MsgBox "Foram excluídas " & ExcluiLinhasPorCriterio(1, 10, 4, "103","105") & " linhas"


End Sub

Fiz algumas alterações em negrito.
Preciso que seja excluida todas as linhas que nao satifazem o critério. Só que nao consigo colocar mais do que um critério e quando coloco um valor alto no segundo critério(10 ex:55000) a macro acusa erro de estouro. Gostaria que me ajudasse nessa questão.

desde já agradeço

webmaster

03-09-2009 14:54:38

Colega,

O código está correto. Porém, mude as variáveis que podem causar estouro de Integer para Long, como fez no cabeçalho da função. Isso deve evitar o estouro.

Estou enganado ou a chamada do método que colocou tem 5 parâmetros enquanto o próprio tem 4 no cabeçalho? Falou alguma coisa no código?

Se o erro persistir, por favor, coloque aqui a linha onde ele ocorre e uma massa de dados para testar.

Abraços

renaut77

03-09-2009 17:28:42

Caro Tomás, obrigado pela dica, realmente estava querendo fazer algo sem fazer as devidas declarações.

Function ExcluiLinhasPorCriterio(ByVal linhaInicial As Long, ByVal linhaFinal As Long, ByVal colunaCriterio As Long, ByVal criterio As Long, ByVal criterio2 As Long) As Long

Dim linhasExcluidas As Long
Dim i As Integer
linhasExcluidas = 0
With ActiveSheet
i = linhaInicial
While i < linhaFinal
If CStr(.Cells(i, colunaCriterio).Value) <> criterio Then
.Rows(i).Delete
linhasExcluidas = linhasExcluidas + 1
Else
i = i + 1
End If

If CStr(.Cells(i, colunaCriterio).Value) <> criterio2 Then
.Rows(i).Delete
linhasExcluidas = linhasExcluidas + 1
Else
i = i + 1
End If
Wend
End With
ExcluiLinhasPorCriterio = linhasExcluidas
End Function

Sub Executa()
MsgBox "Foram excluídas " & ExcluiLinhasPorCriterio(1, 10, 4, 100, 200) & " linhas"

End Sub

Muito obrigado!

webmaster

03-09-2009 18:28:38

Vamos lá,

Precisei fazer algumas mudanças no código e algumas considerações. Primeiro, o próprio:

Código: Selecionar todos

Function ExcluiLinhasPorCriterio(ByVal linhaInicial As Long, ByVal linhaFinal As Long, ByVal colunaCriterio As Long, ByVal criterio As String, ByVal criterio2 As String) As Long
    Dim linhasExcluidas As Long
    Dim i As Long
    linhasExcluidas = 0
    With ActiveSheet
        i = linhaInicial
        While i < linhaFinal
            If Not CStr(.Cells(i, colunaCriterio).Value) = criterio And Not CStr(.Cells(i, colunaCriterio).Value) = criterio2 Then
                .Rows(i).Delete
                linhaFinal = linhaFinal - 1
                linhasExcluidas = linhasExcluidas + 1
            Else
                i = i + 1
            End If
        Wend
    End With
    ExcluiLinhasPorCriterio = linhasExcluidas
End Function

Sub Executa()
    Application.ScreenUpdating = False
    MsgBox "Foram excluídas " & ExcluiLinhasPorCriterio(2, ActiveSheet.UsedRange.Rows.Count, 2, "1", "2") & " linhas"
    Application.ScreenUpdating = True
End Sub
Os detalhes:

Fiz a chamada a função ExcluiLinhasPorCriterio iniciando na linha 2, pois a primeira linha geralmente é um título, o que pode gerar erros na execução.
Como linha final, estou indicando o total de linhas ocupadas da planilha usando ActiveSheet.UsedRange.Rows.Count.
Adicionei dentro do If principal um linhaFinal = linhaFinal - 1 para garantir que após a exclusão de linhas, o código não invada outras áreas, o que pode também gerar erros.
Mantive os critérios no cabeçalho como String e não Long. Faça como quiser, mas na hora da comparação, os tipos precisam ser equivalentes.
Reduzi a comparação ao mesmo If.

Fiz alguns testes aqui e funcionou bem. Para completar, um desafio:

Essa função vai ficar realmente completa se os critérios forem ao invés de duas variáveis (criterio1 e criterio2), fossem um array. Assim, a função seria para qualquer situação.

Abraços

renaut77

04-09-2009 16:18:37

Muito grato pela atenção Tomás.
Vou trabalhar para fazer conforme sua intrução.
Pense que tenho 21 critérios em um arquivo de 60 mil linhas. Está funcionando mas o código ficou enorme e demora mais de 30 minutos para executar.
Com Arrays ele pode ficar mais rápido?

Obrigado.

webmaster

04-09-2009 17:26:31

Colega,

Usando o Application.ScreenUpdating = False e Application.ScreenUpdating = True antes de depois da chamada da função, com fiz no último exemplo já ajuda um pouco. Tirando isso, não tem muita saída, já que a comparação é linha a linha.

Para não dizer que literalmente não há saída, talvez usando ADO com SQL surta algum efeito, mais aí a história é outra.

Abraços

renaut77

10-09-2009 16:56:09

Caro Tomás pode me ajudar...
Estou tentando colocar os Arrays. Veja:
Detalhe a sub executa é onde eu peço para executar todo o processo. Ela está dando um erro em critérios, indicando que não há referencias.

Function ExcluiLinhasPorCriterio(ByVal linhaInicial As Long, ByVal linhaFinal As Long, ByVal colunacriterio As Long) As Long

criterios(0 To 21) As Long
criterios(0) = 100
criterios(1) = 103
criterios(2) = 105
criterios(3) = 109
criterios(4) = 110
criterios(5) = 103
criterios(6) = 111
criterios(7) = 119
criterios(8) = 140
criterios(9) = 184
criterios(10) = 190
criterios(11) = 193
criterios(12) = 195
criterios(13) = 500
criterios(14) = 503
criterios(15) = 505
criterios(16) = 510
criterios(17) = 515
criterios(18) = 540
criterios(19) = 584
criterios(20) = 593
criterios(21) = 595



Dim linhasExcluidas As Long
Dim i As Long
linhasExcluidas = 0
With ActiveSheet
i = linhaInicial
While i < linhaFinal
If CStr(.Cells(i, colunacriterio).Value) <> criterios Then
.Rows(i).Delete
linhasExcluidas = linhasExcluidas + 1
Else
i = i + 1
End If

End If
Wend
End With
ExcluiLinhasPorCriterio = linhasExcluidas
End Function
Sub Executa()
MsgBox "Foram excluidas" & ExcluiColunasPorCriterio(1, 65536, 1) & "Colunas"
End Sub
***************************************************************************************************************************
Sub Executa1()
MsgBox "Foram excluídas " & ExcluiLinhasPorCriterio(1, 65536, 4, criterios) & " linhas"
End Sub

webmaster

10-09-2009 17:30:01

Colega,

A idéia está correta, mas você não pode comparar um valor com um array. É preciso verificar cada valor dentro do array, um a um, provavelmente em um laço While. Há alguma dificuldade nisso?

Abraços

renaut77

10-09-2009 19:32:12

Por favor pode exemplificar?

webmaster

11-09-2009 01:53:07

Colega,

O código fica assim:

Código: Selecionar todos

Function ExcluiLinhasPorCriterio(ByVal linhaInicial As Integer, ByVal linhaFinal As Integer, ByVal colunaCriterio As Integer, ByRef criterios() As String) As Integer
    Dim linhasExcluidas As Integer
    Dim i As Integer
    linhasExcluidas = 0
    With ActiveSheet
        i = linhaInicial
        While i < linhaFinal
            Dim indice As Integer
            Dim excluirLinha As Boolean
            excluirLinha = False
            For indice = 1 To UBound(criterios) Step 1
                If CStr(.Cells(i, colunaCriterio).Value) = criterios(indice) Then
                    excluirLinha = True
                End If
            Next
            If excluirLinha Then
                .Rows(i).Delete
                linhasExcluidas = linhasExcluidas + 1
            Else
                i = i + 1
            End If
        Wend
    End With
    ExcluiLinhasPorCriterio = linhasExcluidas
End Function

Sub Executa()
    Dim cidades(3) As String
    cidades(1) = "London"
    cidades(2) = "São Paulo"
    cidades(3) = "Madrid"
    
    MsgBox "Foram excluídas " & ExcluiLinhasPorCriterio(1, 200, 6, cidades) & " linhas"
End Sub
Em anexo segue uma planilha com o código funcionando.

Espero que ajude. Abraços

renaut77

15-09-2009 11:08:42

Olá Tomás veja como ficou o código agora com arrays.
Acrescentei tambem a exclusão de algumas colunas, mas ainda nao está adptado com array.

Public Function ExcluiLinhas() As Long

Dim a As Variant
a = Array(100, 103, 105, 109, 110, 111, 119, 140, 184, 190, 193, 195, 500, 503, 505, 510, 515, 540, 584, 593, 595)

Dim n_criterio As Integer
Dim exclui As Boolean
Dim k1, k2 As Integer

n_criterio = 21
k1 = 2
exclui = True

While Range("A" & k1).Value <> ""

exclui = True
k2 = 0
While (exclui = True) And (k2 < n_criterio)
If Range("D" & k1).Value = a(k2) Then
exclui = False
Else
k2 = k2 + 1
End If
Wend

If exclui = True Then
Cells.Rows(k1).Delete
Else
k1 = k1 + 1
End If

Wend

End Function
Public Function ExcluiColunas() As Long

While (Range("A1").Value <> "Desenho")
Columns("A:A").Delete
Wend

While (Range("B1").Value <> "Data")
Columns("B:B").Delete
Wend

While (Range("C1").Value <> "Hora")
Columns("C:C").Delete
Wend

While (Range("D1").Value <> "Causal")
Columns("D:D").Delete
Wend

While (Range("E1").Value <> "Num Imput")
Columns("E:E").Delete
Wend

While (Range("F1").Value <> "Quant")
Columns("F:F").Delete
Wend

While (Range("G1").Value <> "")
Columns("G:G").Delete
Wend

While (Range("H1").Value <> "")
Columns("H:H").Delete
Wend

End Function
Sub Executa()
Dim a, b As Long
a = ExcluiColunas()
b = ExcluiLinhas()

End Sub

Aqui no final a sub apenas executa de forma enxuta as duas functions.

Tomás voce tem algum exercicio diferente deste para eu poder dar continuidade nos meus estudos?

Desde já fico grato.

webmaster

15-09-2009 14:18:26

Colega,

Como diria um professor meu, não tem segredo:

Código: Selecionar todos

Public Sub CaminhoDasPedras()
     Inicio:
     Call Ler()
     Call Praticar()
     Call Vivenciar()
     Goto Inicio
End Sub
Para entender melhor a sátira: http://www.tomasvasquez.com.br/artigos/ ... _GoTo.html

Abraços

dbacellar

03-07-2010 20:37:16

Caro Tomás,

Encontrei na internet o seu código para a macro de excluir linhas de acordo com um critério. Primeiro gostaria de agradecer por dividir na rede estas macros, me ajudaram bastante.

Eu tenho uma dúvida: existe uma alternativa ao MsgBox? De forma que não seja necessário "clicar" "Ok" para cada vez que exclui as linhas, em cada critério.

Grato.

Sds.

webmaster

05-07-2010 01:03:05

Colega,

Tite o MsgBox. Resolve não? Ou entendi errado?

Abraços