Número da última célula ativa, preenchida no Excel com VBA

Objetivo: Descobrir a última linha preenchida de uma planilha.

Para seguir este passo-a-passo realize o download do arquivo texto:

1. Abra o Excel
2. No Excel, clique em abrir e altere o campo “Arquivos do tipo” para “Todos os arquivos (*.*)“, localize desta forma o arquivo texto que você realizou o download
3. Selecione o arquivo texto, ainda sem clicar em abrir
4. Com o arquivo selecionado pressione CTRL+SHIFT+ENTER, este comando fará com que o arquivo seja aberto na íntegra em uma só coluna.

Criando a Macro

• Clique na guia Desenvolvedor
• Clique sobre o botão Gravar Macro
• Digite um nome para a Macro e digite uma tecla de atalho, sendo que esta tecla de preferência não deve ser a de nenhum atalho do Excel, por isso dê preferência a utilizar letras maiúsculas, bastando para isso digitar Shift+ a letra, no caso utilizando a letra “O”.
• Em Armazenar macro em: selecione “Pasta de trabalho pessoal de macros”, para que fique disponível em todo o sistema
• Em Descrição digite uma descrição sobre a macro

• Clique em OK. Atenção, a partir deste momento o Excel estará gravando todas as suas ações.
• Selecione a coluna A
• Clique na Guia Dados
• Clique no botão Texto para Colunas. O sistema irá abrir o Assistente para conversão de texto em colunas
• Clique em Delimitado e no botão Avançar

• Na tela que segue você determina o delimitador das tabelas, que neste caso será “Ponto e vírgula” , sendo que você pode selecionar qualquer delimitador, ou ainda na tela anterior selecionar Largura fixa e determinar aonde o arquivo texto será cortado para criar as colunas.
• Após determinar o delimitador “;”  você notará linhas verticais delimitando as colunas
• Clique em Avançar
• Na próxima tela você poderá determinar os tipos de dados de cada uma das colunas, no nosso caso vamos deixar como está.
• Clique em Concluir.
• Selecione toda a planilha e clique entre duas colunas para que o Excel realize o ajuste automático de todas as colunas.
• Clique na célula A1.
• Clique na guia Desenvolvedor.
• Clique no botão Parar Gravação.

Pronto! A sua macro para abrir este tipo de arquivo texto estará pronta, sendo que para testar você deve fechar o arquivo texto sem Salvar o arquivo texto, porém salvando a Macro criada caso o Excel questione.

Depois abra o arquivo texto seguindo os mesmos passos do início do post, selecionando o arquivo e pressionando as teclas CTRL+SHIFT+ENTER.

Após abrir o arquivo execute a macro, pressionando a tecla de atalho que você determinou na gravação, no nosso caso seria CTRL+SHIFT+o.

O seu arquivo será separado em colunas automaticamente dentro do padrão determinado.

Dica: Você pode melhorar este exemplo realizando formatações diretamente sobre as colunas criadas, adicionando uma linha no início e colocando cabeçalhos, caso o seu arquivo não tenha, colocando negrito, etc.

Esta foi a primeira parte do trabalho, na segunda parte vamos colocar um total no final da coluna Freight

Para isso vamos ter que descobrir qual é a última linha preenchida na coluna Freight, para que ele não sobreponha os dados.

Clique na guia Desenvolvedor, depois em Visual Basic e localize o Module 1 aonde estará o seu código fonte.

Após isso crie a variável acima de todo o código dentro da procedure, logo após o seu início.

Dim lUltimaLinhaAtiva As Long

Depois de todos os códigos da procedure que são referentes a importação digite o código abaixo que busca o número da última linha preenchida, na qual vamos colocar a fórmula de soma logo após ela.

lUltimaLinhaAtiva = Worksheets("Plan1").Cells(Worksheets("Plan1").Rows.Count.End(xlUp).Row

Neste código o VBA irá buscar a última célula preenchida na coluna 8(H, referente ao campo Freight) e retornar o número da linha(Row).

Desta forma sabemos aonde iremos colocar a fórmula.

Para inserir a fórmula digite abaixo desta linha:

Range(“H” & lUltimaLinhaAtiva+1).Formula = “=SUM(H2:H” & lUltimaLinhaAtiva & “)”

Entendendo a linha:

Nesta linha você está dizendo que o endereço H & lUltimalinhaAtiva +1 (H832 sendo que a última linha ativa é 831 e é somado 1 no código fonte) está recebendo uma fórmula “.Formula=” a fórmula SOMA do intervalo H2 á H831, lembrando que o valor da variável lUltimaLinhaAtiva é 831.

A função passada tem que estar sempre em inglês, como no exemplo SUM, mas quando você verificar na planilha ela estará no idioma selecionado no Excel.

Para quem está mais atento e acompanha o blog, nota que este recurso, de descobrir a última célula preenchida de uma coluna, é utilizado com grande frequência em códigos VBA, e é muito útil e tem diversas aplicações, bastando adaptar o seu uso.

No download você tem o arquivo texto para você treinar.

Baixe a planilha

Abraço

Marcos Rieper