Células e Intervalos no Excel VBA

Quer aprender a trabalhar com Células e Intervalos no Excel VBA? Então você está no lugar certo!

Quando se trabalha com o Excel, gastamos a maior parte do nosso tempo lidando com células e intervalos, não é mesmo?

Por outro lado, se você pretende automatizar ao seu trabalho no Excel usando o VBA, então será necessário aprender a trabalhar com células e intervalos usando o VBA. Existem diferentes coisas que se pode fazer com intervalos de células usando o VBA como, por exemplo, selecionar, copiar, mover, editar, etc.

Neste artigo, mostraremos através de exemplos, como trabalhar com células e intervalos no Excel usando o VBA. Confira!

Células e Intervalos no Excel VBA

Dica para iniciantes: Todos os códigos mencionados neste tutorial precisam ser colocados no editor VBA. Acesse a guia Desenvolvedor > Visual Basic. No painel de edição, clique em Inserir > Módulo e cole os códigos na área em branco à direita.

Selecionando uma célula/intervalo no Excel VBA

Para trabalhar com células e intervalos no Excel usando o VBA, não é necessário selecioná-los, pois na maioria dos casos, é melhor não selecionar células ou intervalos (como veremos).

De qualquer modo, é importante que você leia essa primeira etapa e entenda como ela funciona. Isso será fundamental para o seu aprendizado de VBA e muitos conceitos abordados aqui serão usados ​​ao longo deste tutorial.

Então, vamos começar com um exemplo muito simples.

Selecionando uma única célula usando o VBA

Se você quiser selecionar uma única célula na planilha ativa (por exemplo, A1), então você pode usar o código abaixo:

Sub SelecionarCelula()
Range("A1").Select
End Sub

O código acima tem a parte obrigatória ‘Sub‘ e ‘End Sub‘ e uma linha de código que seleciona a célula A1.

Range(“A1”) informa ao VBA o endereço da célula na qual queremos nos referir.

Select é um método do objeto Range e seleciona as células/intervalo especificadas no objeto Range. As referências de célula precisam ser colocadas entre aspas duplas.

Observe que, como você deseja selecionar a célula na planilha ativa, basta especificar o endereço da célula. Mas se você quiser selecionar a célula em outra planilha (digamos Plan2), primeiro você precisa ativar a Plan2 e, em seguida, selecionar a célula desejada. Veja o exemplo abaixo:

Sub SelecionarCelula()
Worksheets("Plan2").Activate
Range("A1").Select
End Sub

Da mesma forma, você também pode ativar uma pasta de trabalho, depois ativar uma planilha específica e selecionar uma célula.

Sub SelecionarCelula()
Workbooks("Despesas.xlsx").Worksheets("Plan2").Activate
Range("A1").Select
End Sub

Note que, ao se referir a pastas de trabalho, é necessário usar o nome completo junto com a extensão do arquivo (no exemplo acima usamos .xlsx). Caso a pasta de trabalho nunca tenha sido salva, você não precisará usar a extensão de arquivo.

De maneira geral, esses exemplos não são muito úteis, mas você verá mais adiante neste tutorial como podemos usar os mesmos conceitos para copiar e colar células no Excel usando o VBA.

Do mesmo modo como selecionamos uma célula, também podemos selecionar um intervalo. No caso de um intervalo, pode ser um intervalo de tamanho fixo ou um intervalo de tamanho variável.

Em um intervalo de tamanho fixo, você saberá o tamanho do intervalo e poderá usar o tamanho exato no código do VBA. Mas com um intervalo de tamanho variável, você não tem ideia do tamanho do intervalo e precisa usar um pouco de mágica VBA.

Vamos ver como fazer isso.

Selecionando um intervalo de tamanho fixo

No exemplo abaixo estamos selecionando o intervalo fixo A1:C50

Sub SelecionarIntervalo()
Range("A1:C50").Select
End Sub

Outra maneira de fazer isso é usando o código abaixo:

Sub SelecionarIntervalo()
Range("A1", "C50").Select
End Sub

O código acima pega o endereço da célula superior esquerda (A1) e o endereço da célula inferior direita (C50) e seleciona o intervalo inteiro. Essa opção se torna útil quando você está trabalhando com intervalos de tamanho variável (como veremos quando a propriedade End for abordada mais adiante neste tutorial).

Se você quiser fazer a seleção em uma pasta de trabalho diferente ou em uma planilha diferente, será necessário informar ao VBA os nomes exatos desses objetos.

Por exemplo, o código abaixo selecionaria o intervalo A1:C20 na planilha Plan2 na pasta de trabalho Livro2.

Sub SelecionarIntervalo()
Workbooks("Despesas.xlsx").Worksheets("Plan2").Activate
Range("A1:C50").Select
End Sub

Agora, e se você não souber quantas linhas existem? E se você quiser selecionar todas as células que possuem um valor? Nesses casos, você precisa usar os métodos mostrados na próxima seção (seleção de intervalos de tamanho variável).

Selecionando um intervalo de tamanho variável

Existem diferentes maneiras de selecionar um intervalo de células. O método escolhido dependerá de como os dados são estruturados. Nesta seção, abordaremos algumas técnicas que são realmente úteis quando você trabalha com intervalos no VBA.

Usando a propriedade CurrentRange

Caso você não saiba quantas linhas/colunas possuam dados, então você poderá usar a propriedade CurrentRange do objeto Range.

A propriedade CurrentRange abrange todas as células preenchidas em um intervalo de dados. Veja o exemplo abaixo:

Sub SelectCurrentRegion () 
Range ("A1"). CurrentRegion.Select 
End Sub

O método acima é excelente para casos onde você tem tabela sem linhas/colunas em branco.

Mas se você tiver linhas/colunas em branco em sua planilha, este método não selecionará as linhas após encontrar linhas/ colunas em branco. Na imagem abaixo, o método CurrentRegion seleciona os dados até a linha 10, já que a linha 11 está em branco.

Nesses casos, talvez você queira usar a propriedade UsedRange da propriedade Worksheet Object.

Usando a propriedade UsedRange

UsedRange permite que você se refira a quaisquer células que foram alteradas. Portanto, o código abaixo selecionaria todas as células usadas na planilha ativa.

Sub SelectUsedRegion () 
ActiveSheet.UsedRange.Select 
End Sub

Note que no caso de você ter uma célula distante que tenha sido usada, ela seria considerada pelo código acima e todas as células até que a célula usada fosse selecionada.

Usando a propriedade End

A propriedade End permite que você selecione a última célula preenchida. Isso permite imitar o comando CTRL + Seta.

Suponha-se que você tenha um conjunto de dados como mostrado abaixo e deseja selecionar rapidamente a última célula preenchida na coluna A.

O problema aqui é que a lista poderá aumentar e você não saberá quantas células estão preenchidas. Se você tiver que fazer isso usando o teclado, poderá selecionar a célula A1 e, em seguida, usar a tecla de CTRL + seta para baixo e ela selecionará a última célula preenchida na coluna.

Agora vejamos como isso pode ser feito usando o VBA. Essa técnica é útil quando você deseja pular para a última célula preenchida em uma coluna cujo tamanho pode variar.

Sub IrParaUltimaCelulaPreenchida()
Range("A1").End(xlDown).Select
End Sub

Do mesmo modo, você pode usar o End (xlToRight), por exemplo para pular para a última célula preenchida em uma linha.

Sub IrParaUltimaCelulaPreenchida()
Range("A1").End(xlToRight).Select
End Sub

Agora, se você quiser selecionar a coluna inteira em vez de pular para a última célula preenchida, então basta usar o código abaixo:

Sub SelecionarCelulasPreenchidas()
Range("A1", Range("A1").End(xlDown)).Select
End Sub

No código acima, usamos a primeira e a última referência da célula que precisamos selecionar. Não importa quantas células preenchidas existam, o código acima selecionará todos.

Lembre-se do exemplo acima, onde selecionamos o intervalo A1:C50 usando a seguinte linha de código:

Range(“A1″,”C20”)

Aqui A1 era a célula superior esquerda e C50 era a célula inferior direita no intervalo. Podemos usar a mesma lógica na seleção de intervalos de tamanho variável. Mas como não sabemos o endereço exato da célula inferior direita, usamos a propriedade End para obtê-la.

Em Range(“A1”, Range(“A1”).End(xlDown)),”A1″ refere-se à primeira célula e Range(“A1”).End(xlDown) refere-se à última célula. Como fornecemos as duas referências, o método Select seleciona todas as células entre essas duas referências.

Do mesmo modo, também é possível selecionar um conjunto de dados inteiro que tenha várias linhas e colunas. O código abaixo selecionaria todas as linhas/colunas preenchidas a partir da célula A1.

Sub SelecionarCelulasPreenchidas()
Range("A1", Range("A1").End(xlDown).End(xlToRight)).Select
End Sub

No código acima, usamos Range(“A1”). End (xlDown) .End (xlToRight) para obter a referência da célula preenchida no canto inferior direito do conjunto de dados.

Diferença entre o uso de CurrentRegion e End

Se você estiver se perguntando por que usar a propriedade End para selecionar o intervalo preenchido quando tivermos a propriedade CurrentRegion, então deixe-nos dizer a diferença.

Com a propriedade End, você pode especificar a célula inicial. Por exemplo, se você tiver seus dados em A1:C50, mas a primeira linha for cabeçalhos, você poderá usar a propriedade End para selecionar os dados sem os cabeçalhos (usando o código abaixo).

Sub SelecionarCelulasPreenchidas()
Range("A2", Range("A2").End(xlDown).End(xlToRight)).Select
End Sub

Por outro lado, a propriedade CurrentRegion selecionaria automaticamente todo o conjunto de dados, incluindo os cabeçalhos.

Como copiar células / intervalos usando o VBA

Conforme mencionado no início do artigo, selecionar uma célula não é necessário para executar ações nela. Você verá nesta seção como copiar células e intervalos sem selecioná-los. Para isso vamos começar com um exemplo simples.

Copiando uma única célula

Se você quiser copiar a célula A1 e colá-la na célula C1, o código abaixo fará isso.

Sub CopiarCelula()
Range("A1").Copy Range("C1")
End Sub

Note que o método de cópia do objeto de intervalo copia a célula (assim como CTRL + C) e a cola no destino especificado. Neste exemplo, o destino é especificado na mesma linha em que você usa o método Copy. Mas se você quiser tornar seu código ainda mais legível, você pode usar o código abaixo:

Sub CopyCell()
Range("A1").Copy Destination:=Range("C1")
End Sub

Os códigos acima irão copiar e colar o valor, bem como formatação e fórmulas. Como você já deve ter notado, o código acima copia a célula sem selecioná-la, ou seja, não importa onde você esteja na planilha, o código irá copiar a célula A1 e a colar na C1.

Além disso, observe que o código acima sobrescreveria qualquer código existente na célula C1. Se você quiser que o Excel informe se já houver algo na célula C1 sem sobrescrevê-la, use o código abaixo:

Sub CopiarCelula()
If Range("C1") <> "" Then
Response = MsgBox("Deseja sobrescrever os dados existentes", vbYesNo)
End If
If Response = vbYes Then
Range("A1").Copy Range("C1")
End If
End Sub

Copiando um intervalo de tamanho fixo

Se você deseja copiar A1:C50 em J1:M50, então você poderá usar o código abaixo:

Sub CopiarIntervalo()
Range("A1:C50").Copy Range("J1")
End Sub

Na célula de destino é necessário apenas especificar o endereço da célula superior esquerda. O código cola automaticamente o intervalo exato copiado para o destino. Você pode usar o mesmo script para copiar dados de uma planilha para outra.

O código abaixo copia A1:C50 da planilha ativa para Plan2.

Sub CopiarIntervalo()
Range("A1:C50").Copy Worksheets("Plan2").Range("A1")
End Sub

O código acima copia os dados da planilha ativa. Portanto, verifique se a planilha que possui os dados é a planilha ativa antes de executar o código. Por segurança, você também pode especificar o nome da planilha ao copiar os dados.

Sub CopiarIntervalo()
Worksheets("Plan1").Range("A1:C50").Copy Worksheets("Plan2").Range("A1")
End Sub

No código acima, não importa qual planilha está ativa, pois ele sempre irá copiar os dados da Plan1 e irá colar na Plan2.

Você também pode copiar um intervalo nomeado usando seu nome em vez da referência. Por exemplo, se você tiver um intervalo nomeado chamado ‘Vendas’, poderá usar o código abaixo para copiar esses dados para Plan2.

Sub CopiarIntervalo()
Range("Vendas").Copy Worksheets("Plan2").Range("A1")
End Sub

Se o escopo do intervalo nomeado for a pasta de trabalho inteira, você não precisa estar na planilha que tem o intervalo nomeado para executar esse código. Como o intervalo nomeado tem o escopo da pasta de trabalho, você pode acessá-lo de qualquer folha usando esse código.

Se você tiver uma tabela com o nome Tabela1, você pode usar o código abaixo para copiá-la para Plan2.

Sub CopiarTabela()
Range("Tabela1[#All]").Copy Worksheets("Plan2").Range("A1")
End Sub

Você também pode copiar um intervalo para outra pasta de trabalho. No exemplo a seguir, estamos copiando a tabela do Excel (Tabela 1) para a pasta de trabalho Despesas.xlsx.

Sub CopyCurrentRegion()
Range("Tabela1[#All]").Copy Workbooks("Despesas.xlsx").Worksheets("Plan1").Range("A1")
End Sub

Vale lembrar que esse código funcionaria somente se a pasta de trabalho já estiver aberta.

Copiando um intervalo de tamanho variável

Uma opção para copiar intervalos de tamanho variável é simplesmente convertê-los em intervalos nomeados e usar os códigos conforme mostrado na seção anterior.

Mas se você não puder fazer isso, você também tem a opção de usar o CurrentRegion ou a propriedade End.

O código abaixo copia a região atual na planilha ativa e cola na Plan2.

Sub CopyCurrentRegion()
Range("A1").CurrentRegion.Copy Worksheets("Plan2").Range("A1")
End Sub

Se você quiser copiar a primeira coluna do seu conjunto de dados até a última célula preenchida e colá-la na Plan2, você pode usar o código abaixo:

Sub CopyCurrentRegion()
Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Plan2").Range("A1")
End Sub

Se você deseja copiar as linhas e as colunas, use o código abaixo:

Sub CopyCurrentRegion()
Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy Worksheets("Plan2").Range("A1")
End Sub

Atribuindo intervalos a variáveis ​​de objeto

Até aqui nós usamos o endereço completo das células como, por exemplo, Workbooks(“Despesas.xlsx”).Worksheets(“Plan1”).Range(“A1”)).

Para tornar seu código mais gerenciável, você pode atribuir esses intervalos a variáveis ​​de objeto e, em seguida, usar essas variáveis. Por exemplo, no código abaixo, atribuímos o intervalo de origem e destino às variáveis ​​de objeto e usamos essas variáveis ​​para copiar dados de um intervalo para outro.

Sub CopiarIntervalo()
Dim IntervaloOrigem As Range
Dim IntervaloDestino As Range
Set IntervaloOrigem = Worksheets("Plan1").Range("A1:D20")
Set IntervaloDestino = Worksheets("Plan2").Range("A1")
IntervaloOrigem.Copy IntervaloDestino
End Sub

Começamos declarando as variáveis ​​como objetos Range. Em seguida, atribuímos o intervalo a essas variáveis ​​usando a instrução Set. Uma vez que o intervalo foi atribuído à variável, você pode simplesmente usar a variável.

Inserindo dados na próxima célula vazia (Usando Input Box)

Você pode usar Input Boxes para permitir que o usuário insira os dados. Por exemplo, suponha que você tenha o conjunto de dados abaixo e deseja inserir o registro de vendas, você pode usar a Input Box no VBA. Usando um código, podemos nos certificar de que os dados serão preenchidos na próxima linha em branco.

Sub InserirDados()
Dim RefRange As Range
Set RefRange = Range("A1").End(xlDown).Offset(1, 0)
Set Categoria = RefRange.Offset(0, 1)
Set Quantidade = RefRange.Offset(0, 2)
Set Valor = RefRange.Offset(0, 3)
RefRange.Value = RefRange.Offset(-1, 0).Value + 1
Categoria.Value = InputBox("Categoria")
Quantidade.Value = InputBox("Quantidade")
Valor.Value = InputBox("Valor")
End Sub

O código acima usa a Input box do VBA para obter as entradas do usuário e, em seguida, insere as entradas nas células especificadas.

Observe que não usamos referências de célula exatas. Em vez disso, usamos a propriedade End and Offset para encontrar a última célula vazia e preencher os dados nela.

Este código está longe de ser utilizável. Por exemplo, se você inserir uma sequência de texto quando a caixa de entrada solicitar quantidade ou montante, você perceberá que o Excel permite isso. Você pode usar uma condição If para verificar se o valor é numérico ou não e, em seguida, permiti-lo.

Looping através de células / intervalos

Até agora vimos como selecionar, copiar e inserir os dados nas células e nos intervalos. Nesta seção, veremos como percorrer um conjunto de células / linhas / colunas em um intervalo. Isso pode ser útil quando você deseja analisar cada célula e executar alguma ação com base nela.

Por exemplo, se você quiser destacar todas as terceiras linhas da seleção, será necessário percorrer e verificar o número da linha. Da mesma forma, se você quiser destacar todas as células negativas alterando a cor da fonte para vermelho, será necessário percorrer e analisar o valor de cada célula.

Aqui está o código que percorrerá as linhas nas células selecionadas e destacará as linhas alternativas.

Sub DestacarLinhas()
Dim MeuIntervalo As Range
Dim MinhaLinha As Range
Set MeuIntervalo = Selection
For Each MinhaLinha In MeuIntervalo.Rows
If MinhaLinha.Row Mod 2 = 0 Then
MinhaLinha.Interior.Color = vbCyan
End If
Next MinhaLinha
End Sub

O código acima usa a função MOD para verificar o número da linha na seleção. Se o número da linha for par, ele será destacado na cor ciano.

Aqui está outro exemplo em que o código passa por cada célula e destaca as células que possuem um valor negativo.

Sub DestacarLinhas()
Dim MeuIntervalo As Range
Dim MinhaCelula As Range
Set MeuIntervalo = Selection
For Each MinhaCelula In MeuIntervalo
If MinhaCelula < 0 Then
MinhaCelula.Interior.Color = vbRed
End If
Next MinhaCelula
End Sub

Note que você pode fazer a mesma coisa usando a formatação condicional (que é dinâmica e uma maneira melhor de fazer isso). Este exemplo é apenas com o propósito de mostrar como o loop funciona com células e intervalos no VBA.

E então, o que achou da explicação sobre Células e Intervalos no Excel VBA? Deixe o seu comentário abaixo, gostaríamos muito de conhecer a sua opinião! Para conferir mais dicas de Excel, continue acompanhando o Excel Easy!