5 Formas de crear rangos dinámicos VBA Excel.

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.

  1. Forma 1. UsedRange
  2. Forma 2. Ultima Fila – Columna de la hoja con xlUp.
  3. Forma 3. xlTypeLastCell
  4. Forma 4. CurrentRegion.
  5. 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.

rango_dinamico_vba_excel
Tiempo de funcionamiento de cada macro, en segundos

Una recomendación adicional, utiliza siempre Application.ScreenUpdating antes de cualquier macro, el tiempo de reacción se mejora en promedio en un 75%.

rangos_dinamicos_vba_excel1
Tiempo de funcionamiento de cada macro con ScreenUpdating, en segundos.

Si tienes alguna duda sobre rangos dinámicos en vba excel, nos leemos abajo.

5 Formas de crear rangos dinámicos VBA Excel.
Scroll hacia arriba