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. 

Os códigos VBA: 

Versões de 2007: 

Dim DernLigne As Long 
DernLigne = Range("A65536").End(xlUp).Row

A partir de 2007: 

Dim DernLigne As Long 
DernLigne = Range("A1048576").End(xlUp).Row

Impossível fazer a portabilidade de seu arquivo de uma versão para outra. 

Primeira linha vazia de uma coluna

Geralmente é útil saber que ela está na primeira linha vazia de uma coluna, principalmente, para poder colar os dados nos dados já existentes. Os diferentes códigos dados aqui, são válidos para qualquer versão do Excel.

Para um looping (ciclo)

Nós vamos, neste artigo, fechar todas as células da coluna C para determinar a primeira célula vazia encontrada. 

Dim lig As Long 
Lig = 1 'primeira linha a verificar
Do While Not IsEmpty(Range("C" & Lig))
    Lig = Lig + 1
Loop
MsgBox "A primeira linha vazia coluna C é a linha: " & Lig

Este procedimento tem inconvenientes, principalmente a velocidade de execução. Na verdade, em tabelas muito grandes, o ciclo em todas as linhas é o mais lento de todas as possibilidades. Para informação em um quadro de 60 000 linhas, ela durou 1,05 segundos no meu PC. O segundo problema é o caso onde você teris, nesta coluna, as células vazias. O procedimento irá parar na célula vazia encontrada pela primeira vez e te enviará o número da linha correspondente. Nem sempre é prático. 

Combinação CTRL+Seta para baixo

Você pode ter notado, quando digita, <ital> [a partir de sua planilha Excel, depois de selecionar a primeira célulSeta para baixa da coluna (C1 no exemplo)]</ ital> simultaneamente 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, ignorando as SendKeys. Trata-se da propriedade End do Excel, à qual é atribuído o parâmetro obrigatório xlDown. Com este procedimento nós enviamos o número da últimalinha não-vazia da coluna, para obter aprimeira linha vazia, basta adicionar 1... 

Dim DLig As Long 
DLig = Range("C1").End(xlDown).Row + 1

Observação: se você tem uma( ou mais) célula vazia no meio da sua coluna. É a linha dela que será enviada por sua função... 

Combinação CTRL+Seta para o alto

Ao contrário da combinação anterior, quando você seleciona a última célula da coluna (C65536 C1048576 dependendo da sua versão) e que você digite simultaneamente as tecla CTRL + Seta para cima, você vai selecionar a última célula não-vazia de sua coluna. O código VBA correspondente, independente de sua versão seria, em pseudo-código: 

Range ("C" número_da_lina_total_de_minha_planilha) .End (xlUp) .Row 
No entanto, o número total de linha de uma planilha independentemente da versão é expressa em VBA por: Rows.Count (contagem das linhas). 
Daí resulta o código: 

Dim maLigne As Long 
maLigne = Range("C" & Rows.Count).End(xlUp).Row + 1

Observação: Este método, quando sua coluna é inteiramente vazia, volta o valor 2. Ora, na realidade, a primeira linha vazia é a 1. Para igualá-la, será conveniente fazer um teste na primeira célula de sua coluna. Do tipo: 

If Range("C1") <> "" Then
    maLigne = Range("C" & Rows.Count).End(xlUp).Row + 1
Else
  maLigne = 1
End If

O método Find

O método Find (pesquisar) é usado para localizar um valor em um intervalo de células. Pode-se lhe adicionar configurações como o sentido e/ou a direção da pesquisa: (Mais informações). O método Find retorna a primeira ocorrência que encontrar. Então, basta pesquisar: 

O que: "qualquer coisa", 

Ou: "coluna(3)" (= coluna C), 

Direção: "por colunas" 

Sentido: "para o alto" (= precedente) 
De fato, nós 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 : 

Dim Ligne As Long
Ligne = Columns(3).Find("*", , , , xlByColumns, xlPrevious).Row + 1

Observação: Como as maneiras de fazer precedentes, convém adicionar 1 para obter o número da primeira linha vazia.Este método envia um erro quando a coluna é inteiramente vazia. Neste caso, utilize um test na primeira célula: 

If Range("C1") <> "" Then
    Ligne = Columns(3).Find("*", , , , xlByColumns, xlPrevious).Row + 1
Else
  Ligne = 1
End If

O lado positivo deste método é que você utiliza o número da coluna e não a letra. Isto faz ciclos mais fáceis e mais facilmente compreensíveis. 

Exemplo de um ciclo na colunas A até a J para determinar qual é a mais longa: 

Dim Lign As Long, numCol As Integer, tempLig As Long, Colonne As String
For numCol = 1 to 10 'colonnes A à J
  If Cells(1, numCol) <> "" Then 'test si 1ère ligne vide
      Ligne = Columns(numCol).Find("*", , , , xlByColumns, xlPrevious).Row
      If Ligne > tempLig Then tempLig = Ligne: Colonne = Chr(64 + numCol)
  End If
Next
MsgBox "La colonne la plus remplie est : " & Colonne

Este método tem um texto diferente do End(xlUp). Notar-se-áque a mesma possibilidade é perfeitamente possível para End(xlDown) com a ajuda da seguinte sintaxe: 

Ligne = Columns(3).Find("", , , , xlByColumns, xlNext).Row

Nota : Sem necessidade de +1, visa-se a primeira célula vazia. Desta maneira, encontramos o mesmo inconveniente que End(xlDown), ou seja uma célula vazia no meio da nossa coluna envia um resultado errado. 

O método SpecialCells

Este método permite encontrar a última linha de sua planilha, que tenha ou não linhas vazias. 
Observação: Atenção, de qualquer maneira, no caso de supressão de linhas, para recuperar o real número da última linha, será necessário salvar o arquivo. 

Dim Ligne As Long
Ligne = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

Vê-se aqui, inútil de referenciar um Range ou uma coluna. Trata-se da última linha absoluta da planilha. Este método comporta igualmente uma propriedade Address, útil para detectar igualmente a coluna: 

Dim Adresse As String
Adresse = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address

Última coluna não vazia de uma linha

Você será confrontado também algum dia 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 ciclos nas colunas, esta informação pode ser necessária. Os exemplos são todos baseados no fato de buscar a última coluna preenchida da linha 4. Por outro lado, contrariamente ao capítulo precedente, nós não ligaremos o número da última coluna preenchida, não a primeira coluna vazia... 

As explicações serão menos completas que para as linhas, porque é exatamente igual. 

Abaixo você verá alguns métodos possíveis de ser usados. 

Columns.Count & End(xlToLeft)

O método End tem um parâmetro de direção como vimos. Esta direção pode muito bem ser Para à esquerda. Neste ponto, precisamos apenas da última coluna do nosso arquivo (Columns.Count) e voltar para à esquerda. O equivalente das teclas CTRL+ Seta para a esquerda. 

Dim DernCol As Integer 
DernCol = Cells(4, Cells.Columns.Count).End(xlToLeft).Column

Nota: Não precisa mais uma variável do tipo longo. Aqui o tipo integer é amplamente suficiente. 

End(xlToRight)

Dim DernCol As Integer 
DernCol = Range("A4").End(xlToRight).Column

Atenção em caso de célula vazia na llinha 4... 

O método SpecialCells

Dim DernCol As Integer 
DernCol = 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

Atenção com as células vazias... 

Conclusão

Saiba que existe outros procedimentos.. Tentei, aqui, descrever o máximo de situações que você poderá encontrar. Se estas manipulações não forem suficientes, não deixe de postar esse problema no fórum CCM apropriado.

via CCM

• Twitter
• Facebook
• Google +
• Linkedin
• Pinterest