VBA – Rango Dinámico

Este artículo demostrará cómo crear un Rango Dinámico en Excel VBA.

Declarar un rango específico de celdas como una variable en Excel VBA nos limita a trabajar sólo con esas celdas en particular. Al declarar rangos dinámicos en Excel, ganamos mucha más flexibilidad sobre nuestro código y la funcionalidad que puede realizar.

Referenciando Rangos y Celdas

Cuando hacemos referencia al objeto Rango o Celda en Excel, normalmente nos referimos a ellos codificando la fila y las columnas que necesitamos.

Propiedad Range

Utilizando la propiedad Range, en las líneas de código de ejemplo que aparecen a continuación, podemos realizar acciones sobre este rango como cambiar el color de las celdas, o ponerlas en negrita.

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

Propiedad Cells

Del mismo modo, podemos utilizar la propiedad Cells para referirnos a un rango de celdas haciendo referencia directa a la fila y a la columna en la propiedad cells. La fila tiene que ser siempre un número, pero la columna puede ser un número o una letra entre comillas. Por ejemplo, la dirección de la celda A1 puede ser referenciada como

Cells(1,1)

O

Cells(1, "A")

Para utilizar la propiedad Cells para referenciar un rango de celdas, debemos indicar el inicio del rango y el final del mismo. Por ejemplo, para referenciar el rango A1: A6 podríamos utilizar esta sintaxis

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

A continuación podemos utilizar la propiedad Cells para realizar acciones sobre el rango según las líneas de código de ejemplo que aparecen a continuación:

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

Rangos Dinámicos con Variables

A medida que el tamaño de nuestros datos cambia en Excel (es decir, utilizamos más filas y columnas que los rangos que hemos codificado), sería útil que los rangos a los que nos referimos en nuestro código también cambiaran. Utilizando el objeto Range anterior podemos crear variables para almacenar los números máximos de fila y columna del área de la hoja de cálculo de Excel que estamos utilizando, y utilizar estas variables para ajustar dinámicamente el objeto Range mientras se ejecuta el código.

Por ejemplo

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

Última fila de la columna

Como hay 1048576 filas en una hoja de trabajo, la variable ultimaFila irá al fondo de la hoja y luego utilizará la combinación especial de la tecla .End(xlUp) para ir a la última fila utilizada en la hoja de trabajo – esto nos dará el número de la fila que necesitamos en nuestro rango.

Última Columna en la Fila

De manera similar, la ultimaColumna se moverá a la Columna XFD que es la última columna en una hoja de trabajo, y luego usará la combinación especial de la tecla .End(xlToLeft) para ir a la última columna usada en la hoja de trabajo – esto nos dará el número de la columna que necesitamos en nuestro rango. Por lo tanto, para obtener todo el rango que se utiliza en la hoja de trabajo, podemos ejecutar el siguiente código:

Sub ObtenerRango()
  Dim ultimaFila As Integer
  Dim ultimaColumna As Integer
  Dim rng As Range

'utilizar la ultimaFila para ayudar a encontrar la última fila del rango
  ultimaFila = Range("A1048576").End(xlUp).Row
'utilizar la ultimaColumna para ayudar a encontrar la última columna del rango
  ultimaColumna = Range("XFD" & ultimaFila).End(xlToLeft).Column
  Set rng = Range(Cells(1, 1), Cells(ultimaFila, ultimaColumna))
'msgbox para mostrarnos el rango
  MsgBox "Rango es: " & rng.Address
  
End Sub

SpecialCells – Última Celda

También podemos utilizar el método SpecialCells del objeto Range para obtener la  última fila y columna utilizadas en una hoja de cálculo.

Sub UsarCeldasEspeciales()
  Dim ultimaFila As Integer
  Dim ultimaColumna As Integer
  Dim rng As Range
  Dim rngBegin As Range
  Set rngBegin = Range("A1")
  ultimaFila = rngBegin.SpecialCells(xlCellTypeLastCell).Row
  ultimaColumna = rngBegin.SpecialCells(xlCellTypeLastCell).Column
  Set rng = Range(Cells(1, 1), Cells(ultimaFila, ultimaColumna))
'msgbox para mostrarnos el rango
  MsgBox "Rango es: " & rng.Address
End Sub

UsedRange

El método Used Range incluye todas las celdas que tienen valores en ellas en la hoja de trabajo actual.

Sub RangoUsado()
  Dim rng As Range
  Set rng = ActiveSheet.UsedRange
'msgbox para mostrarnos el rango
  MsgBox "Rango es: " & rng.Address
End Sub

CurrentRegion

La región actual difiere de UsedRange en que mira las celdas que rodean a una celda que hemos declarado como rango inicial (es decir, la variable rngBegin en el ejemplo de abajo), y luego mira todas las celdas que están ‘adjuntas’ o asociadas a esa celda declarada. Si se produce una celda en blanco en una fila o columna, entonces la CurrentRegion dejará de buscar más celdas.

Sub RegionActual()
  Dim rng As Range
  Dim rngBegin As Range
  Set rngBegin = Range("A1")
  Set rng = rngBegin.CurrentRegion
'msgbox para mostrarnos el rango
  MsgBox "Rango es: " & rng.Address
End Sub

Si utilizamos este método, tenemos que asegurarnos de que todas las celdas del rango que necesitamos están conectadas sin que haya filas o columnas en blanco entre ellas.

Rango Nombrado

También podemos hacer referencia a los Rangos Nombrados en nuestro código. Los Rangos Nombrados pueden ser dinámicos en la medida en que cuando se actualizan o insertan datos, el Nombre del Rango puede cambiar para incluir los nuevos datos. Este ejemplo cambiará la fuente a negrita para el nombre del rango «Enero»

Sub RangoNombrado()
  Dim rng As Range
  Set rng = Range("Enero")
  rng.Font.Bold = True
End Sub

Como verá en la imagen siguiente, si se añade una fila en el nombre del rango, entonces el nombre del rango se actualiza automáticamente para incluir esa fila.

Si volvemos a ejecutar el código de ejemplo, el rango afectado por el código sería C5:C9 mientras que en el primer caso habría sido C5:C8.

Tablas

Podemos hacer referencia a las tablas (haga clic para obtener más información sobre la creación y manipulación de tablas en VBA) en nuestro código. Cuando los datos de una tabla en Excel se actualizan o cambian, el código que hace referencia a la tabla se referirá a los datos actualizados de la tabla. Esto es particularmente útil cuando se hace referencia a tablas dinámicas que están conectadas a una fuente de datos externa.

Utilizando esta tabla en nuestro código, podemos referirnos a las columnas de la tabla por los encabezados de cada columna, y realizar acciones sobre la columna según su nombre. A medida que las filas de la tabla aumentan o disminuyen según los datos, el rango de la tabla se ajustará en consecuencia y nuestro código seguirá funcionando para toda la columna de la tabla. Por ejemplo:

Sub EliminarColumnaDeTabla()
  ActiveWorkbook.Worksheets("Hoja1").ListObjects("Tabla1").ListColumns("Octubre").Delete
End Sub