Este artículo demostrará cómo crear un rango dinámico en Excel VBA.
Declarar un rango específico de celdas como variable en Excel VBA nos limita a trabajar solo con esas celdas en particular. Al declarar rangos dinámicos en Excel, obtenemos mucha más flexibilidad sobre nuestro código y la funcionalidad que puede realizar.
Hacer referencia a rangos y celdas
Cuando hacemos referencia al objeto Range o Cell en Excel, normalmente nos referimos a ellos codificando la fila y las columnas que necesitamos.
Propiedad de rango
Usando la propiedad del rango, en las líneas de código de ejemplo a continuación, podemos realizar acciones en este rango, como cambiar el color de las celdas o poner las celdas en negrita.
| 12 | Rango ("A1: A5"). Font.Color = vbRedRango ("A1: A5"). Font.Bold = True |
Propiedad de las celdas
De manera similar, podemos usar la propiedad de celdas para hacer referencia a un rango de celdas haciendo referencia directamente a la fila y la columna en la propiedad de celdas. La fila debe ser siempre un número, pero la columna puede ser un número o una letra entre comillas.
Por ejemplo, se puede hacer referencia a la dirección de celda A1 como:
| 1 | Celdas (1,1) |
O
| 1 | Celdas (1, "A") |
Para usar la propiedad Cells para hacer referencia a un rango de celdas, necesitamos indicar el inicio del rango y el final del rango.
Por ejemplo, para hacer referencia al rango A1: A6, podríamos usar esta sintaxis a continuación:
| 1 | Rango (celdas (1,1), celdas (1,6) |
Luego, podemos usar la propiedad Cells para realizar acciones en el rango según las líneas de código de ejemplo a continuación:
| 12 | Rango (celdas (2, 2), celdas (6, 2)). Font.Color = vbRedRango (celdas (2, 2), celdas (6, 2)). Font.Bold = True |
Rangos dinámicos con variables
A medida que el tamaño de nuestros datos cambia en Excel (es decir, usamos más filas y columnas que los rangos que hemos codificado), sería útil si los rangos a los que nos referimos en nuestro código también cambiaran. Usando el objeto Range anterior, podemos crear variables para almacenar los números máximos de filas y columnas del área de la hoja de cálculo de Excel que estamos usando, y usar estas variables para ajustar dinámicamente el objeto Range mientras se ejecuta el código.
Por ejemplo
| 1234 | Dim lRow como enteroDim lCol como enterolRow = Rango ("A1048576"). Fin (xlUp) .RowlCol = Rango ("XFD1"). Fin (xlToLeft) .Column |
Última fila en columna
Como hay 1048576 filas en una hoja de trabajo, la variable lRow irá al final de la hoja y luego usará la combinación especial de la tecla Fin más la tecla Flecha arriba para ir a la última fila usada en la hoja de trabajo; esto nos dará el número de la fila que necesitamos en nuestro rango.
Última columna de la fila
De manera similar, lCol se moverá a la Columna XFD, que es la última columna en una hoja de trabajo, y luego usará la combinación de teclas especiales de la tecla Fin más la tecla Flecha izquierda para ir a la última columna utilizada en la hoja de trabajo; esto nos dará la número de la columna que necesitamos en nuestro rango.
Por lo tanto, para obtener el rango completo que se usa en la hoja de trabajo, podemos ejecutar el siguiente código:
| 1234567891011 | Sub GetRange ()Dim lRow como enteroDim lCol como enteroAtenuar como rangolRow = Rango ("A1048576"). Fin (xlUp) .Row'use lRow para ayudar a encontrar la última columna en el rangolCol = Rango ("XFD" & lRow) .End (xlToLeft) .ColumnEstablecer rng = Rango (Celdas (1, 1), Celdas (lRow, lCol))'msgbox para mostrarnos el rangoMsgBox "El rango es" & rng.AddressEnd Sub |
SpecialCells - LastCell
También podemos usar el método SpecialCells del objeto Range para obtener la última fila y columna utilizada en una hoja de trabajo.
| 123456789101112 | Sub UseSpecialCells ()Dim lRow como enteroDim lCol como enteroAtenuar como rangoDim rngBegin As RangeEstablecer rngBegin = Rango ("A1")lRow = rngBegin.SpecialCells (xlCellTypeLastCell) .RowlCol = rngBegin.SpecialCells (xlCellTypeLastCell) .ColumnEstablecer rng = Rango (Celdas (1, 1), Celdas (lRow, lCol))'msgbox para mostrarnos el rangoMsgBox "El rango es" & rng.AddressEnd Sub |
Rango usado
El método de rango usado incluye todas las celdas que tienen valores en la hoja de trabajo actual.
| 123456 | Sub UsedRangeExample ()Atenuar como rangoEstablecer rng = ActiveSheet.UsedRange'msgbox para mostrarnos el rangoMsgBox "El rango es" & rng.AddressEnd Sub |
Región actual
La región actual difiere de UsedRange en que mira las celdas que rodean una celda que hemos declarado como un rango de inicio (es decir, la variable rngBegin en el ejemplo a continuación), y luego mira todas las celdas que están 'adjuntas' o asociadas a esa celda declarada. Si aparece una celda en blanco en una fila o columna, CurrentRegion dejará de buscar más celdas.
| 12345678 | Sub CurrentRegion ()Atenuar como rangoDim rngBegin As RangeEstablecer rngBegin = Rango ("A1")Establecer rng = rngBegin.CurrentRegion'msgbox para mostrarnos el rangoMsgBox "El rango es" & rng.AddressEnd Sub |
Si usamos este método, debemos asegurarnos de que todas las celdas del rango que necesita estén conectadas sin filas o columnas en blanco entre ellas.
Rango con nombre
También podemos hacer referencia a rangos con nombre 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"
| 12345 | Sub RangeNameExample ()Atenuar como rangoEstablecer rng = Rango ("enero")rng.Font.Bold = = VerdaderoEnd Sub |
Como verá en la imagen siguiente, si se agrega una fila al nombre del rango, el nombre del rango se actualiza automáticamente para incluir esa fila.
Si luego volvemos a ejecutar el código de ejemplo, el rango afectado por el código sería C5: C9 mientras que en la primera instancia habría sido C5: C8.
Mesas
Podemos hacer referencia a tablas (haga clic para obtener más información sobre la creación y manipulación de tablas en VBA) en nuestro código. A medida que se actualizan o cambian los datos de una tabla en Excel, el código que hace referencia a la tabla se referirá a los datos de la tabla actualizados. Esto es particularmente útil cuando se hace referencia a tablas dinámicas que están conectadas a una fuente de datos externa.
Usando esta tabla en nuestro código, podemos referirnos a las columnas de la tabla por los encabezados en cada columna y realizar acciones en la columna de acuerdo con su nombre. A medida que las filas de la tabla aumentan o disminuyen de acuerdo con 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:
| 123 | Sub DeleteTableColumn ()ActiveWorkbook.Worksheets ("Hoja1"). ListObjects ("Tabla1"). ListColumns ("Proveedor"). EliminarEnd Sub |
