VBA: última linha não vazia (todas as versões)
Entre as versões 2003 e 2007, o número de linhas que pode atingir o Excel em uma planilha evoluiu consideravelmente, passando de 65.536 para 1.048.576. Com isso, há problemas de portabilidade dos arquivos entre as versões devido aos códigos VBA diferentes antes e depois da edição 2007 do programa. Veja aqui alguns métodos para definir a primeira célula vazia da planilha e corrigir essa dificuldade.
Este procedimento tem desvantagens, principalmente no que se refere à velocidade de execução. Na verdade, em tabelas muito grandes, o looping em todas as linhas é a opção mais lenta. Para se ter uma ideia, em um quadro de 60 mil linhas, ela durou 1,05 segundos no meu PC. O segundo problema seria, se você tivesse, nesta coluna, células vazias. O procedimento vai parar na primeira célula vazia encontrada e ela vai te enviar o número da linha correspondente. Isso nem sempre é prático.
Trata-se da propriedade End do Excel, à qual é atribuído o parâmetro obrigatório xlDown. Com este procedimento, envia-se o número da última linha não vazia da coluna. Para obter a primeira linha vazia, basta adicionar 1:
Observação: se você tem uma (ou mais) célula vazia no meio da sua coluna é esta linha que será devolvida por sua função.
O código VBA correspondente, independente de sua versão, seria em pseudocódigo:
No entanto, o número de linha total de uma planilha, seja qual for a versão, é expressa, em VBA, por
Observação: este método, quando sua coluna estiver inteiramente vazia, devolve o valor 2. Na realidade, a primeira linha vazia é a 1. Para remediar isso, é melhor fazer um teste na primeira célula de sua coluna.
o quê: "qualquer coisa";
onde: "coluna(3)" (= coluna C);
direção: "por colunas";
sentido: "para cima" (= anterior).
Na verdade, vamos reproduzir aqui o método End(xlUp) graças ao Find, pesquisando a primeira célula cheia da nossa coluna, o que resulta, em VBA, em:
Observação: como os procedimentos anteriores, convém adicionar 1 para obter o número da primeira linha vazia. Este método devolve um erro quando a coluna estiver inteiramente vazia. Neste caso, utilize um teste na primeira célula. A vantagem deste método é que você utiliza o número da coluna e não a letra. Isto faz ciclos mais fáceis e compreensíveis.
Exemplo de um looping na colunas de A à J para determinar qual é a mais longa:
Vimos que este método tem uma escrita diferente do End(xlUp). Saiba que a mesma coisa é possível para o End(xlDown) com a ajuda da seguinte sintaxe:
Observação: não há mais necessidade de +1, visa-se diretamente a primeira célula vazia. Desta maneira, encontramos o mesmo problema que End(xlDown), ou seja, uma célula vazia no meio da nossa coluna devolve um resultado errado.
Observação: cuidado no caso de supressão de linhas para recuperar o número real da última linha, pois você deverá salvar a pasta:
Vemos aqui que é inútil referenciar uma linha ou uma coluna. Trata-se da última linha absoluta da planilha. Este método também comporta uma propriedade Address, prática para detectar a coluna:
Observação: não precisa mais de uma variável do tipo Long. Aqui o tipo Integer basta.
ou:
Foto: © Microsoft.
- Primeira linha vazia de uma coluna
- Por looping (ciclo)
- Última coluna não vazia de uma linha
- Conclusão
Primeira linha vazia de uma coluna
Geralmente, é bom saber qual é a primeira linha vazia de uma coluna, principalmente para poder colar conteúdo sobre os dados já existentes. Os diferentes códigos dados aqui são válidos para qualquer versão do Excel.Por looping (ciclo)
Nesta dica, vamos fechar todas as células da coluna C para determinar a primeira célula vazia:Dim lig As Long
Lin = 1 'primeira linha a ser verificada
Do While Not IsEmpty(Range("C" & Lin))
Lin = Lin + 1
Loop
MsgBox "A primeira linha vazia coluna C é a linha: " & Lin
Este procedimento tem desvantagens, principalmente no que se refere à velocidade de execução. Na verdade, em tabelas muito grandes, o looping em todas as linhas é a opção mais lenta. Para se ter uma ideia, em um quadro de 60 mil linhas, ela durou 1,05 segundos no meu PC. O segundo problema seria, se você tivesse, nesta coluna, células vazias. O procedimento vai parar na primeira célula vazia encontrada e ela vai te enviar o número da linha correspondente. Isso nem sempre é prático.
Combinação Ctrl + Seta para baixo
Você deve ter notado que quando você pressiona simultaneamente as teclas Ctrl e Seta para baixo, a célula selecionada torna-se a última célula não vazia de sua coluna. Esta manipulação de teclas pode ser realizada como um código VBA, sem passar pelas SendKeys.Trata-se da propriedade End do Excel, à qual é atribuído o parâmetro obrigatório xlDown. Com este procedimento, envia-se o número da última linha não vazia da coluna. Para obter a primeira linha vazia, basta adicionar 1:
Dim DLin As Long
DLin = Range("C1").End(xlDown).Row + 1
Observação: se você tem uma (ou mais) célula vazia no meio da sua coluna é esta linha que será devolvida por sua função.
Combinação Ctrl + Seta para cima
Ao contrário da combinação anterior, ao selecionar a última célula da sua coluna (C65536 ou C1048576, dependendo da versão) e pressionar, ao mesmo tempo, as teclas Ctrl + Seta para cima, você vai selecionar a última célula não vazia da coluna.O código VBA correspondente, independente de sua versão, seria em pseudocódigo:
Range("C" número_da_linha_total_de_uma_planilha) .End(xlUp).Row
No entanto, o número de linha total de uma planilha, seja qual for a versão, é expressa, em VBA, por
Rows.Count (contagem das linhas). Resultando no código:
Dim maLinha As Long
minhaLinha = Range("C" & Rows.Count).End(xlUp).Row + 1
Observação: este método, quando sua coluna estiver inteiramente vazia, devolve o valor 2. Na realidade, a primeira linha vazia é a 1. Para remediar isso, é melhor fazer um teste na primeira célula de sua coluna.
O método Find
O método Find (pesquisar) é usado para procurar um valor em um intervalo de células. Nós podemos adicionar configurações como o sentido e/ou a direção da pesquisa. O método Find devolve a primeira ocorrência que encontrar. Então, basta procurar:o quê: "qualquer coisa";
onde: "coluna(3)" (= coluna C);
direção: "por colunas";
sentido: "para cima" (= anterior).
Na verdade, vamos reproduzir aqui o método End(xlUp) graças ao Find, pesquisando a primeira célula cheia da nossa coluna, o que resulta, em VBA, em:
Dim Linha As Long
Linha = Columns(3).Find("*", , , , xlByColumns, xlPrevious).Row + 1
Observação: como os procedimentos anteriores, convém adicionar 1 para obter o número da primeira linha vazia. Este método devolve um erro quando a coluna estiver inteiramente vazia. Neste caso, utilize um teste na primeira célula. A vantagem deste método é que você utiliza o número da coluna e não a letra. Isto faz ciclos mais fáceis e compreensíveis.
Exemplo de um looping na colunas de A à J para determinar qual é a mais longa:
Dim Lin As Long, númCol As Integer, tempLin As Long, Colonne As String
For numCol = 1 to 10 'colunas A à J
If Cells(1, númCol) <> "" Then 'teste se primeira linha vazia
Linha = Columns(númCol).Find("*", , , , xlByColumns, xlPrevious).Row
If Linha > tempLig Then tempLig = Linha: Coluna = Chr(64 + númCol)
End If
Next
MsgBox "A coluna mais cheia é: " & Coluna
Vimos que este método tem uma escrita diferente do End(xlUp). Saiba que a mesma coisa é possível para o End(xlDown) com a ajuda da seguinte sintaxe:
Linha = Columns(3).Find("", , , , xlByColumns, xlNext).Row
Observação: não há mais necessidade de +1, visa-se diretamente a primeira célula vazia. Desta maneira, encontramos o mesmo problema que End(xlDown), ou seja, uma célula vazia no meio da nossa coluna devolve um resultado errado.
O método SpecialCells
Este método permite encontrar a última linha de sua planilha, com ou sem linhas vazias.Observação: cuidado no caso de supressão de linhas para recuperar o número real da última linha, pois você deverá salvar a pasta:
Dim Linha As Long Linha = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
Vemos aqui que é inútil referenciar uma linha ou uma coluna. Trata-se da última linha absoluta da planilha. Este método também comporta uma propriedade Address, prática para detectar a coluna:
Dim Adresse As String Endereço = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address
Última coluna não vazia de uma linha
Um dia, você também vai se deparar com o mesmo problema em relação às colunas. Na verdade, nós nem sempre sabemos com antecedência o número de colunas utilizadas na nossa planilha. Para realizar loopings nas colunas, esta informação pode ser indispensável. Os exemplos são todos baseados na busca da última coluna preenchida da linha 4. Por outro lado, contrariamente ao que vimos anteriormente, nós devolveremos o número da última coluna cheia, e não a primeira coluna vazia. As explicações serão menos extensas para as linhas, porque é exatamente a mesma coisa. Veja alguns métodos possíveis.Columns.Count & End(xlToLeft)
O método End tem um parâmetro de direção, como já vimos. Esta direção pode muito bem ser para esquerda. Neste ponto, precisamos apenas partir da última coluna da nossa pasta (Columns.Count) e voltar para a esquerda. O equivalente das teclas Ctrl + Seta para à esquerda:Dim DernCol As Integer ÚltCol = Cells(4, Cells.Columns.Count).End(xlToLeft).Column
Observação: não precisa mais de uma variável do tipo Long. Aqui o tipo Integer basta.
End(xlToRight)
Dim ÚltCol As Integer
ÚltCol = Range("A4").End(xlToRight).Column
O método SpecialCells
Dim ÚltCol As Integer ÚltCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
O método Find
Dim Col As Integer
Col = Rows(3).Find("*", , , , xlByRows, xlPrevious).Column
ou:
Dim Col As Integer
Col = Rows(3).Find("", , , , xlByRows, xlNext).Column
Conclusão
Saiba que existem outros procedimentos. Nesta dica, tentamos descrever o máximo de situações possíveis. Se estas manipulações não forem suficientes, poste o seu problema no fórum do CCM.Foto: © Microsoft.