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

Preencher Lista de Dados
Preencher Lista de Dados
7 de setembro de 2011
Criar botões de atalho para Macros e procedimento VBA Excel
Criar botões de atalho para Macros e procedimento VBA Excel
12 de setembro de 2011

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.

Abraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.