VBA – Intervalo Dinâmico

Este artigo demonstrará como criar um intervalo dinâmico no Excel VBA.

Declarar um intervalo específico de células como uma variável no Excel VBA nos limita a trabalhar apenas com essas células específicas. Ao declarar intervalos dinâmicos no Excel, ganhamos muito mais flexibilidade sobre nosso código e a funcionalidade que ele pode executar.

Referência a Intervalos e Células

Quando fazemos referência ao objeto Range ou Cell no Excel, normalmente nos referimos a eles codificando a linha e as colunas de que precisamos.

Propriedade Range

Usando a propriedade Range, no exemplo de linhas de código abaixo, podemos executar ações nesse intervalo, como alterar a cor das células ou torná-las em negrito.

Range("A1:A5").Font.Color = vbRed
Range("A1:A5").Font.Bold = True

Propriedade Cells

Da mesma forma, podemos usar a propriedade Cells para nos referirmos a um intervalo de células fazendo referência direta à linha e à coluna na propriedade cells. A linha deve ser sempre um número, mas a coluna pode ser um número ou uma letra entre aspas.

Por exemplo, o endereço da célula A1 pode ser referenciado como:

Cells(1,1)

Ou

Cells(1, "A")

Para usar a propriedade Cells para fazer referência a um intervalo de células, precisamos indicar o início e o fim do intervalo.

Por exemplo, para fazer referência ao intervalo A1: A6, poderíamos usar a sintaxe abaixo:

Range(Cells(1,1), Cells(1,6)

Em seguida, podemos usar a propriedade Cells para executar ações no intervalo, conforme o exemplo de linhas de código abaixo:

Range(Cells(2, 2), Cells(6, 2)).Font.Color = vbRed
Range(Cells(2, 2), Cells(6, 2)).Font.Bold = True

Intervalos Dinâmicos com Variáveis

Como o tamanho dos nossos dados muda no Excel (ou seja, usamos mais linhas e colunas do que os intervalos que codificamos), seria útil se os intervalos aos quais nos referimos em nosso código também mudassem. Usando o objeto Range acima, podemos criar variáveis para armazenar os números máximos de linha e coluna da área da planilha do Excel que estamos usando e usar essas variáveis para ajustar dinamicamente o objeto Range enquanto o código estiver em execução.

Por exemplo

Dim lRow as integer
Dim lCol as integer
lRow  = Range("A1048576").End(xlUp).Row
lCol  = Range("XFD1").End(xlToLeft).Column

Última Linha na Coluna

Como há 1048576 linhas em uma planilha, a variável lRow irá até a parte inferior da planilha e, em seguida, usará a combinação especial da tecla End com a tecla de seta para cima para ir até a última linha usada na planilha – isso nos dará o número da linha de que precisamos em nosso intervalo.

Última Coluna na Linha

Da mesma forma, lCol irá para a coluna XFD, que é a última coluna em uma planilha, e usará a combinação especial de teclas End e Seta para a esquerda para ir para a última coluna usada na planilha – isso nos dará o número da coluna de que precisamos em nosso intervalo.

Portanto, para obter o intervalo inteiro usado na planilha, podemos executar o código a seguir:

Sub ObterIntervalo()
  Dim lRow As Integer
  Dim lCol As Integer
  Dim rng As Range
  lRow = Range("A1048576").End(xlUp).Row
'usar o lRow para ajudar a encontrar a última coluna no intervalo
  lCol = Range("XFD" & lRow).End(xlToLeft).Column
  Set rng = Range(Cells(1, 1), Cells(lRow, lCol))
'msgbox para nos mostrar o intervalo
  MsgBox "O intervalo é " & rng.Address
End Sub

SpecialCells – LastCell (Última Célula)

Também podemos usar o método SpecialCells do objeto Range para obter a última linha e coluna usadas em uma planilha.

Sub UsandoSpecialCells()
  Dim lRow As Integer
  Dim lCol As Integer
  Dim rng As Range
  Dim rngBegin As Range
  Set rngBegin = Range("A1")
  lRow = rngBegin.SpecialCells(xlCellTypeLastCell).Row
  lCol = rngBegin.SpecialCells(xlCellTypeLastCell).Column
  Set rng = Range(Cells(1, 1), Cells(lRow, lCol))
'msgbox para nos mostrar o intervalo
  MsgBox "O intervalo é " & rng.Address
End Sub

UsedRange (Intervalo Utilizado)

O método Used Range inclui todas as células que contêm valores na planilha atual.

Sub ExemploUsedRange()
  Dim rng As Range
  Set rng = ActiveSheet.UsedRange
'msgbox para nos mostrar o intervalo
  MsgBox "O intervalo é " & rng.Address
End Sub

CurrentRegion (Região Atual)

A região atual difere de UsedRange porque examina as células ao redor de uma célula que declaramos como intervalo inicial (ou seja, a variável rngBegin no exemplo abaixo) e, em seguida, examina todas as células que estão “anexadas” ou associadas a essa célula declarada. Se ocorrer uma célula em branco em uma linha ou coluna, o CurrentRegion deixará de procurar outras células.

Sub RegiaoAtual()
  Dim rng As Range
  Dim rngBegin As Range
  Set rngBegin = Range("A1")
  Set rng = rngBegin.CurrentRegion
'sgbox para nos mostrar o intervalo
  MsgBox "O intervalo é " & rng.Address
End Sub

Se usarmos esse método, precisaremos nos certificar de que todas as células do intervalo desejado estejam conectadas, sem linhas ou colunas em branco entre elas.

Intervalo Nomeado

Também podemos fazer referência a Named Ranges (Intervalos Nomeados) em nosso código. Os intervalos nomeados podem ser dinâmicos na medida em que, quando os dados são atualizados ou inseridos, o nome do intervalo pode ser alterado para incluir os novos dados.

Este exemplo mudará a fonte para negrito para o nome do intervalo “Janeiro”.

Sub ExemploIntervaloNomeado()
  Dim rng as Range 
  Set rng = Range("Janeiro")
  rng.Font.Bold = = True 
End Sub

Como você verá na figura abaixo, se uma linha for adicionada ao nome do intervalo, o nome do intervalo será atualizado automaticamente para incluir essa linha.

Se executarmos o código de exemplo novamente, o intervalo afetado pelo código será C5:C9, enquanto no primeiro caso teria sido C5:C8.

 

Tabelas

Podemos fazer referência a tabelas (clique para obter mais informações sobre a criação e a manipulação de tabelas no VBA) em nosso código. À medida que os dados de uma tabela no Excel são atualizados ou alterados, o código que se refere à tabela fará referência aos dados atualizados da tabela. Isso é particularmente útil quando se faz referência a tabelas dinâmicas que estão conectadas a uma fonte de dados externa.

Usando essa tabela em nosso código, podemos nos referir às colunas da tabela pelos títulos de cada coluna e executar ações na coluna de acordo com seu nome. À medida que as linhas da tabela aumentam ou diminuem de acordo com os dados, o intervalo da tabela se ajustará de acordo e nosso código ainda funcionará para toda a coluna da tabela.

Por exemplo:

Sub ApagarColunaTabela()
  ActiveWorkbook.Worksheets("Planilha1").ListObjects("Tabela4").ListColumns("Fornecedor").Delete
End Sub