Hola y bienvenidos a una entrada más, hoy vamos a darle una checada a 5 formas en las que podrás crear tus rangos dinámicos en vba excel
Imagínate que no sabes mucho de VBA, usas la grabadora para copiar un rango de celdas y hacer alguna acción con el (copiar, poner un filtro, crear una tabla dinámica, convertir el rango en tabla).
Todo salió de maravilla y cuando quieres volverla a usar con un rango más grande te das cuenta de que no funciona del todo bien. ¿La razón? El rango que seleccionaste con la grabadora queda como un rango fijo, por lo que ahora es necesario crear un rango que se ajuste a tus necesidades.
Aquí abajo te dejo algunos ejemplos que como hacerlo sin romperte mucho la cabeza.
- Forma 1. UsedRange
- Forma 2. Ultima Fila – Columna de la hoja con xlUp.
- Forma 3. xlTypeLastCell
- Forma 4. CurrentRegion.
- Forma 5. Contar el número de filas – columnas.
Forma 1. UsedRange
El método UsedRange nos permite seleccionar un rango en una hoja que ya haya sido activado anteriormente.
Sub PruebaRangos() 'Establece primero la hoja a usar y la celda de inicio Dim sht As Worksheet Set sht = Sheets(1) sht.Select ' Método 1 UsedRange ' Efectivo cuando el proceso es automático y no hay mas datos en la hoja sht.UsedRange.Select End Sub
- Pros: Es un método veloz, de hecho, el mas veloz como veremos en la tabla de abajo.
- Contras: Solo funciona si tu rango a seleccionar es el único valor en la hoja, si hay datos mas abajo o mas a la derecha también los copiara.
Forma 2. Ultima Fila – Columna de la hoja con xlUp.
Este método te permitirá almacenar en una variable la ultima fila y la ultima columna del rango de tu hoja. Es un método un poco más lento que el anterior, pero mas efectivo ya que depende de donde posicionemos nuestra celda de inicio.
Sub PruebaRangos()
Dim sht As Worksheet
Dim LRow, LCol As Long
Dim StartCell As Range
' Establece primero la hoja a usar y la celda de inicio
Set sht = Sheets(1)
sht.Select
Set StartCell = Range("A1")
'Metodo 2 Ultima fila - columna
' Efectivo cuando la tabla esta distribuida equitativamente
LRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column
sht.Range(StartCell, sht.Cells(LRow, LColumn)).Select
End Sub- Pros: Menor margen de error que el método UsedRange ya que permite definir a partir de donde tomar los valores
- Contras: Debemos conocer la posición inicial del rango, además de que, si la primera celda de ultima fila o de la ultima columna esta en blanco, el código no leerá el rango apropiadamente.
Forma 3. xlTypeLastCell
Con la propiedad SpecialCells del método Range podremos ir automáticamente a la ultima fila y columna sin la necesidad de que están tengan valores propiamente, como pasaba arriba.
Sub PruebaRangos()
Dim sht As Worksheet
Dim LRow, LCol As Long
Dim StartCell As Range
' Establece primero la hoja a usar y la celda de inicio
Set sht = Sheets(1)
sht.Select
Set StartCell = Range("A1")
'Metodo 3 ultima celda
'Efectivo cuando quieres todos los valores de una hoja de calculo.
LRow = StartCell.SpecialCells(xlCellTypeLastCell).Row
LColumn = StartCell.SpecialCells(xlCellTypeLastCell).Column
sht.Range(StartCell, sht.Cells(LRow, LColumn)).Select
End Sub- Pros: Nos dirige automáticamente a la ultima celda que este activa y toma su fila – columna de forma más rápida. Ideal si los usuarios no pueden depositar datos de forma manual en la hoja a usar.
- Contras: Es el método mas lento de los aquí descritos, además de que conlleva que una celda mas abajo o mas a la derecha no haya sido activada, lo que puede ocasionar algunos detalles.
Forma 4. CurrentRegion.
La propiedad CurrentRegion nos permite dejar a Excel tratar de identificar la relación que existe entre los valores que queremos tomar como rango. Esto funciona especialmente bien si el rango es una tabla o tiene ya filtros delimitados.
Sub PruebaRangos()
Dim sht As Worksheet
Dim StartCell As Range
' Establece primero la hoja a usar y la celda de inicio
Set sht = Sheets(1)
sht.Select
Set StartCell = Range("A1")
' Metodo 4 Current Region
' Efectivo cuando no tenemos filas o columnas en blanco y el rango esta definido por una tabla
StartCell.CurrentRegion.Select
End Sub- Pros: Funciona desde cualquier parte del rango relacionado, evitando tener que saber en que parte se inicia el rango.
- Contras: Si buscas colocar filtros o tienes filas – columnas en blanco el método fallara inevitablemente.
Forma 5. Contar el número de filas – columnas.
Incluyo este ya que en lo particular es con el que aprendí a programar cuando empecé con mis pininos en VBA, y me parece hoy en día un método con versatilidad ya que nos puede decir cuál es el largo y ancho de nuestros valores y podemos usar eso en algunas itinerancias con el ciclo For.
Sub PruebaRangos()
Dim sht As Worksheet
Dim rng, rng2 As Long
Dim StartCell As Range
' Establece primero la hoja a usar y la celda de inicio
Set sht = Sheets(1)
sht.Select
Set StartCell = Range("A1")
'Metodo 5
' Efectivo para copiar y pegar en ciertos rangos que pueden variar y mantener guardado el largo y ancho
rng = Application.WorksheetFunction.CountA(Range("A1:A1000000")) - 1
rng2 = Application.WorksheetFunction.CountA(Range("1:1")) - 1
Range(StartCell, StartCell.Offset(rng, rng2)).Select
End Sub- Pros: Es un método estable, el que menos variación presento de velocidad en la prueba que hice, adicional de que permite conocer exactamente el largo y ancho de nuestros valores.
- Contras: Al igual que cualquier método de formula de Excel, si los datos tienen valores en blanco en alguna fila – columna, puede generar errores.
EL MEJOR DE ELLOS…
El ultimo, ¡Obvio!
Bueno, no. En realidad, todos funcionan dependiendo tus necesidades y el escenario de tus datos. Si nos vamos a una prueba de velocidades al parecer los primeros dos métodos son los más veloces. La prueba se realizo con un rango de 10000 celdas en la cual las macros copiaban y pegaban los valores en una hoja.
Una recomendación adicional, utiliza siempre Application.ScreenUpdating antes de cualquier macro, el tiempo de reacción se mejora en promedio en un 75%.
Si tienes alguna duda sobre rangos dinámicos en vba excel, nos leemos abajo.
