Select Menu



por Alessandro Trovato

» » » » » » » VBA - Artigo 021 - Diversas maneiras de encontrar a última linha ou coluna com valor
«
Proxima
Postagem mais recente
»
Anterior
Postagem mais antiga

Progredindo em VBA no Microsoft Excel

Diversas maneiras de encontrar a última linha ou coluna com valor

Durante nosso aprendizado em macros no Excel, certamente tivemos algum momento em que tivemos de aprender a encontrar a última linha ou última coluna com valor. Com o tempo aprendemos outras formas diferentes de fazer isso, pois há alguns problemas em alguns métodos.

O primeiro que aprendemos normalmente é o seguinte:

UltimaLinha = Range("A1").End(xlDown).Row
UltimaColuna = Range("A1").End(xlToRight).Column

Tão logo conhecemos esse método aprendemos o problema dele: se houver alguma célula entre a primeira e a última célula, o valor retornado será o da última célula antes da vazia. Assim, logo passamos para o seguinte método:

UltimaLinha = Cells(Planilha1.Rows.Count, 1).End(xlUp).Row
UltimaColuna = Cells(1, Planilha1.Columns.Count).End(xlToLeft).Column
' Planilha1 é o nome da planilha inicial do Excel, aqui apenas para fins didáticos

Esse método costuma ser o mais utilizado, mas deve-se levar em conta que está buscando a última linha em uma coluna específica, no caso a primeira. Se houver valores nas linhas seguintes e a primeira coluna da(s) última(s) linha(s) estiver em branco será retornado um valor errado e a partir daí os dados serão comprometidos.

Um outro método que tem sido bastante divulgado é com o UsedRange:

UltimaLinha = Planilha1.UsedRange.Rows(Planilha1.UsedRange.Rows.Count).Row
UltimaColuna = Planilha1.UsedRange.Columns(Planilha1.UsedRange.Columns.Count).Column

Entretanto, se seu código inclui e apaga linhas ou colunas, o Excel pode passar a dar valores incorretos para UsedRange, o que pode comprometer o bom funcionamento do código.

Felizmente, há uma alternativa que funciona bem em todos os casos e sem contraindicação:

UltimaLinha = Planilha1.Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
UltimaColuna = Planilha1. Cells.Find("*",LookIn:=xlFormulas,  SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Explicando: a pesquisa é feita buscando por qualquer valor (asterisco) em valores ou fórmulas (parâmetro LookIn) na direção indicada (parâmetro SearchOrder) e irá começar do fim para o começo (parâmetro SearchDirection). Pode parecer um tanto complexo à primeira vista, mas entendendo cada parâmetro individualmente dá para compreender o funcionamento.

Esse método usa um comando longo e pode ser cansativo escrever tudo isso toda vez que for preciso encontrar o valor da última linha ou coluna. Solução? Crie uma função para isso:

Function EncontrarUltimaLinha(Planilha As Worksheet) As Long

    EncontrarUltimaLinha = Planilha.Cells.Find("*", LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

End Function

Function EncontrarUltimaColuna(Planilha As Worksheet) As Long

    EncontrarUltimaColuna = Planilha.Cells.Find("*", LookIn:=xlFormulas, _
        SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

End Function

Fica muito mais fácil usar digitar a fórmula usando apenas a planilha desejada como parâmetro:

UltimaLinha = EncontrarUltimaLinha(plProdutos)
UltimaColuna = EncontrarUltimaColuna(plProdutos)

É preciso observar que essas funções retornarão um resultado referente à planilha inteira, mas há casos em que precisamos do valor referente a uma linha ou coluna específica. Podemos adaptar as funções acima para acrescentar um parâmetro opcional:

Function EncontrarUltimaLinha(Planilha As Worksheet, Optional Coluna As Long = 0) As Long

    If Coluna <= 0 Then
        EncontrarUltimaLinha = Planilha.Cells.Find("*", LookIn:=xlFormulas, _
            SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Else
        EncontrarUltimaLinha = Planilha.Columns(Coluna).Find("*", LookIn:=xlFormulas, _
            SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If

End Function

Function EncontrarUltimaColuna(Planilha As Worksheet, Optional Linha As Long = 0) As Long

    If Linha <= 0 Then
        EncontrarUltimaColuna = Planilha.Cells.Find("*", LookIn:=xlFormulas, _
            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Else
        EncontrarUltimaColuna = Planilha.Rows(Linha).Find("*", LookIn:=xlFormulas, _
            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    End If

End Function

Com essas funções ambos casos são contemplados. Se só for informado o parâmetro da planilha, a pesquisa levará em conta a planilha inteira, caso seja informado a linha ou coluna, é somente nela que será verificada.

Perceba que na validação da linha e coluna coloquei menor ou igual, pois caso seja informado um valor negativo passa a considerar a planilha inteira e assim não gera erro de linha ou coluna com valor negativo.

Todos os métodos acima servem para pesquisas na planilha inteira. Em intervalos nomeados o funcionamento é um pouco diferente, pois vai levar em conta somente um intervalo definido. Para encontrar o valor da última linha ou coluna em um intervalo nomeado o jeito mais simples é o seguinte:

UltimaLinha = Range("Clientes").Cells(Range("Clientes").Rows.Count, 1).Row
UltimaColuna = Range("Clientes").Cells(1, Range("Clientes").Columns.Count).Column

Novamente, esse método está levando em conta uma coluna e uma linha específica. Também é possível usar o método usando o Find para encontrar o valor referente ao intervalo inteiro:

UltimaLinha = Range("Clientes").Cells.Find("*", LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
UltimaColuna = Range("Clientes").Cells.Find("*", LookIn:=xlFormulas, _
    SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Espero que este artigo tenha ajudado a perceber as diferenças entre os diversos métodos, bem como os prós e contras de cada um. Até o próximo artigo!

Pedro Martins


Pós-graduando em Business Intelligence e Big Data pela Faculdade Impacta de Tecnologia. Formado em Tecnologia em Eletrônica Digital com Ênfase em Microprocessadores



Autor Pedro Martins

Esta é uma breve descrição no bloco de autor sobre o autor. Você edita-lo, no html
«
Proxima
Postagem mais recente
»
Anterior
Postagem mais antiga

Nenhum comentário

Comentarios