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
