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 Dynamic Ranges no Excel, ganhamos muito mais flexibilidade sobre nosso código e a funcionalidade que ele pode executar.
Intervalos e células de referência
Quando referimos o objeto Range ou Cell no Excel, normalmente nos referimos a eles codificando permanentemente nas linhas e colunas que exigimos.
Propriedade de alcance
Usando a propriedade Range, nas linhas de código de exemplo abaixo, podemos realizar ações neste intervalo, como alterar a cor das células ou torná-las em negrito.
| 12 | Intervalo ("A1: A5"). Font.Color = vbRedIntervalo ("A1: A5"). Font.Bold = True |
Propriedade Células
Da mesma forma, podemos usar a propriedade Cells para fazer referência a um intervalo de células, referenciando diretamente a linha e a 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:
| 1 | Células (1,1) |
Ou
| 1 | Células (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 referência ao intervalo A1: A6, poderíamos usar a sintaxe abaixo:
| 1 | Faixa (células (1,1), células (1,6) |
Podemos então usar a propriedade Cells para realizar ações no intervalo de acordo com as linhas de código de exemplo abaixo:
| 12 | Intervalo (células (2, 2), células (6, 2)). Font.Color = vbRedIntervalo (células (2, 2), células (6, 2)). Font.Bold = True |
Intervalos dinâmicos com variáveis
Como o tamanho de 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 linhas e colunas da área da planilha do Excel que estamos usando e usar essas variáveis para ajustar dinamicamente o objeto Range enquanto o código está sendo executado.
Por exemplo
| 1234 | Dim lRow como inteiroDim lCol como inteirolRow = Range ("A1048576"). End (xlUp) .RowlCol = Range ("XFD1"). End (xlToLeft) .Column |
Última linha na coluna
Como há 1048576 linhas em uma planilha, a variável lRow irá para a parte inferior da planilha e então usará a combinação especial da tecla End mais a tecla de seta para cima para ir para 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, o lCol se moverá para a coluna XFD, que é a última coluna em uma planilha, e então usará a combinação de teclas especiais da tecla End mais a tecla de seta para a esquerda para ir para a última coluna usada na planilha - isso nos dará o número da coluna que precisamos em nosso intervalo.
Portanto, para obter todo o intervalo que é usado na planilha, podemos executar o seguinte código:
| 1234567891011 | Sub GetRange ()Dim lRow As IntegerDim lCol As IntegerDim rng como alcancelRow = Range ("A1048576"). End (xlUp) .Row'use a lRow para ajudar a encontrar a última coluna no intervalolCol = Range ("XFD" & lRow) .End (xlToLeft) .ColumnDefinir rng = intervalo (células (1, 1), células (lRow, lCol))'msgbox para nos mostrar o alcanceMsgBox "Range is" & rng.AddressEnd Sub |
SpecialCells - LastCell
Também podemos usar o método SpecialCells do objeto Range para obter a última linha e coluna usada em uma planilha.
| 123456789101112 | Sub UseSpecialCells ()Dim lRow As IntegerDim lCol As IntegerDim rng como alcanceDim rngBegin As RangeDefinir rngBegin = Range ("A1")lRow = rngBegin.SpecialCells (xlCellTypeLastCell) .RowlCol = rngBegin.SpecialCells (xlCellTypeLastCell) .ColumnDefinir rng = intervalo (células (1, 1), células (lRow, lCol))'msgbox para nos mostrar o alcanceMsgBox "Range is" & rng.AddressEnd Sub |
UsedRange
O Método de intervalo usado inclui todas as células que contêm valores na planilha atual.
| 123456 | Sub UsedRangeExample ()Dim rng como alcanceSet rng = ActiveSheet.UsedRange'msgbox para nos mostrar o alcanceMsgBox "Range is" & rng.AddressEnd Sub |
CurrentRegion
A região atual difere do UsedRange porque olha para as células ao redor de uma célula que declaramos como um intervalo inicial (ou seja, a variável rngBegin no exemplo abaixo) e, em seguida, olha para todas as células que estão 'anexadas' ou associadas para essa célula declarada. Caso ocorra uma célula em branco em uma linha ou coluna, CurrentRegion irá parar de procurar por quaisquer outras células.
| 12345678 | Sub CurrentRegion ()Dim rng como alcanceDim rngBegin As RangeDefinir rngBegin = Range ("A1")Definir rng = rngBegin.CurrentRegion'msgbox para nos mostrar o alcanceMsgBox "Range is" & rng.AddressEnd Sub |
Se usarmos este método, precisamos ter certeza de que todas as células no intervalo que você precisa estão conectadas sem linhas ou colunas em branco entre elas.
Intervalo Nomeado
Também podemos fazer referência a 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 no nome do intervalo “Janeiro”
| 12345 | Sub RangeNameExample ()Dim rng as RangeDefinir rng = Range ("janeiro")rng.Font.Bold = = VerdadeiroEnd Sub |
Como você verá na imagem abaixo, se uma linha for adicionada ao nome do intervalo, o nome do intervalo será atualizado automaticamente para incluir essa linha.
Devemos então executar o código de exemplo novamente, o intervalo afetado pelo código seria C5: C9, enquanto na primeira instância seria C5: C8.
Mesas
Podemos fazer referência a tabelas (clique para obter mais informações sobre como criar e manipular tabelas em VBA) em nosso código. Conforme os dados de uma tabela no Excel são atualizados ou alterados, o código que se refere à tabela se referirá aos dados atualizados da tabela. Isso é particularmente útil ao se referir a tabelas dinâmicas que estão conectadas a uma fonte de dados externa.
Usando esta tabela em nosso código, podemos nos referir às colunas da tabela pelos cabeçalhos de cada coluna e realizar ações na coluna de acordo com seu nome. Conforme as linhas na tabela aumentam ou diminuem de acordo com os dados, o intervalo da tabela será ajustado de acordo e nosso código ainda funcionará para toda a coluna da tabela.
Por exemplo:
| 123 | Sub DeleteTableColumn ()ActiveWorkbook.Worksheets ("Folha1"). ListObjects ("Tabela1"). ListColumns ("Fornecedor"). ExcluirEnd Sub |
