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.
Cordial saludo, muchas gracias por compartir tu conocimiento, gracias a ti he aprendido mucho de powerapps y powerautomate.
Tengo una consulta ¿Cómo convierto el rango seleccionado en una tabla? lo he intentado pero no me da.
hola.
Para crear una tabla en vba puedes usar esto.
Set src = Range(«B5:B10″)
Set ws = ActiveSheet
ws.ListObjects.Add( SourceType:=xlSrcRange, Source:=src, _
xlListObjectHasHeaders:=xlYes, tablestyleName:=»TableStyleMedium28»).Name = «TuTabla»
Hola, estas formas están muy bien, pero, ¿qué ocurre si el número de filas o de columnas es cambiante, y no sé dónde va a empezar o terminar el rango?. Mi problema es que quiero insertar valores desde VBA a una columna cuyo encabezado es el día del mes, pero la tabla no tiene los fines de semana, y el día 5 no siempre estará en posición 5. Lo que quiero hacer es muy simple: Cómo indicar a VBA que quiero insertar un dato en la celda cuyo encabezado es, por ejemplo, «MARTES», ó «DÍA 5», y cuya fila sea el ID de una persona, por ejemplo, «PEPITO02».
¿Es posible hacer esto que digo?, llevo dos días buscando en webs inglesas y en español y no encuentro nada, pese a que encuentro que para muchos informes que tengo que hacer es muy necesario.
Muchas gracias!
Hola.
Hay varias formas de hacer eso, yo creo que la mas funcional es.
1. En la fila de los encabezados crea un do Until para revisar valor por valor de la celda
2. cuando encuentres el valor terminar tu Do y guardas el Rango en una celda tipo Range.
3. Con esto ya sabes donde empezar y creas tu procedimiento hacia las filas.
Hola, estas formas están muy bien, pero, ¿qué ocurre si el número de filas o de columnas es cambiante, y no sé dónde va a empezar o terminar el rango?. Mi problema es que quiero insertar valores desde VBA a una columna cuyo encabezado es el día del mes, pero la tabla no tiene los fines de semana, y el día 5 no siempre estará en posición 5. Lo que quiero hacer es muy simple: Cómo indicar a VBA que quiero insertar un dato en la celda cuyo encabezado es, por ejemplo, «MARTES», ó «DÍA 5», y cuya fila sea el ID de una persona, por ejemplo, «PEPITO02».
¿Es posible hacer esto que digo?, llevo dos días buscando en webs inglesas y en español y no encuentro nada, pese a que encuentro que para muchos informes que tengo que hacer es muy necesario.
Muchas gracias!
Hola.
Hay varias formas de hacer eso, yo creo que la mas funcional es.
1. En la fila de los encabezados crea un do Until para revisar valor por valor de la celda
2. cuando encuentres el valor terminar tu Do y guardas el Rango en una celda tipo Range.
3. Con esto ya sabes donde empezar y creas tu procedimiento hacia las filas.
Buenas tardes, estoy haciendo un ejercicio de compras pero presenta sub totales, estuve implementando formulas normales aunque he trabajado un poco con vba pero no llego al resultado
ARTICULO (CONCEPTO) FECHA CANTIDAD VALOR TOTAL 5%COMISION
ZELLE $ 1900,00
PRESTOBARBA MAX 3 330 $ 2,95 $ 973,50 $ 48,68
DESODORANTE SPEED STICK 390 $ 1,00 $ 390,00 $ 19,50
ELEVADOR DE OCTANAJE 272 $ 1,07 $ 291,04 $ 14,55
TOTAL aquí suma total
ZELLE $ 2000,00
VITAMINA C 1000 MG 60 $ 13,99 $ 839,40 $ 41,97
SPLENDA 1200 SOBRES 35 $ 13,99 $ 489,65 $ 24,48
FASCILISTAS KRAFT 96 UN 32 $ 6,29 $ 201,28 $ 10,06
VITAMINA SOBRE 90 UN 5 $ 15,79 $ 78,95 $ 3,95
TOTAL
ZELLE $ 1500,00
REPUESTOS MAX 3X5X4 36 $ 31,01 $ 1116,36 $ 55,82
total crédito $ 739,31
TOTAL
ZELLE 26/08/2021 $ 3000,00
La idea es que me sume los totales (para tener subtotales por cada compra) que estén dentro de los rangos «ZELLE» y «TOTAL», entre ellos puede haber cualquier cantidad de compras y como se puede ver, en la misma tabla existen varias compras que inician agregando un valor de «ZELLE» y debería totalizar al agregar la palabra «TOTAL» en la primera columna.
Hola David.
La verdad es que no entiendo para nada la tabla asi como la pone el blog 😐
Si gustas enviarme un ejemplo a svallejo@excelcute.com con gusto lo revisamos.
Saludos.
Tengo una duda, estoy usando el metodo 5 pero no he logrado que me seleccione y copie toda la tabla, solo lo esta haciendo para la primera columna. En ese caso cual seria el codigo?
Hola Juan.
Con el metodo 5, tus columnas estan en la fila 1?
Ya que este metodo cuenta cuantas columnas con valores tienes dentro del rango que le digas (en mi ejemplo 1:1 es todo lo de la primera fila)
Checa que tengas el rango correcto y con valores y vuelve a probar
Saludos!
Hola
Soy una aprendiz de VBA y estoy en un proyecto para crear un cotizador de Hoteles (Son muchos hoteles). Estos, tiene tarifas variables en diferentes periodos, y de acuerdo al tipo de habitación. Entonces he creado una que me genera los periodos y los inserta en la hoja del hotel correspondiente. En la columna de las fechas (dias) de ese periodo, la macro funciona perfectamente con cada período que genero. Sin embargo, yo necesito que los valores tarifas, se copien desde la Hoja llamada CrearPeriodo hasta la hoja del hotel.
La hoja del Hotel cuando se crea tambien desde una macro en una hoja llamada CrearHotel, queda con un nombre determinado por la concatenación de dos celdas. Entonces supongamos que este se llama Maloka Standar.
En otra hoja CrearPeriodo se introducen los datos para crear el periodo en la hoja creada desde CrearHotel
A1 Titulo Crear Periodo Hotel
C1 Nombre del Hotel (Se establece solo al correr la macro que crea esta Hoja)
A2 Desde (El usuario indica fecha inicial por ejemplo 15/01/2022)
A3 Hasta Desde (El usuario indica fecha final por ejemplo 30/01/2022)
A4 Individual (El usuario indica Tarifa, por ejemplo $75)
A5 Doble (El usuario indica Tarifa, por ejemplo $60)
A6 Triple (El usuario indica Tarifa, por ejemplo $60)
A7 Cuadruple (El usuario indica Tarifa, por ejemplo $60)
A8 Quintuple (El usuario indica Tarifa, por ejemplo $60)
A9 Niños (El usuario indica Tarifa, por ejemplo $30)
A10 Infantes (El usuario indica Tarifa, por ejemplo $10)
Al correr la macro, de generar periodo este hace un AutoFill y copia la serie de fechas en la columna A. y se posiciona en la ultima fila e la columna A para esperar la creación de otro periodo.
Ahora requiero, y no tengo idea de como hacerlo que las tarifas tambien se copien en el mismo rango que ocupe cada periodo.
Fecha Individual/Doble/Triple /Cuadruple/Quintuple/ Adicional/ Niño/ Infante
15/01/2021 75 / 60/ 60
16/01/2021 75 / 60/ 60
17/01/2021 75 / 60/ 60
…
30/01/2021 75 / 60 / 60
Aquí la macro
Sub Periodo()
Dim Desde As Date
Dim Hasta As Date
Dim Hotel As String
Dim Habitación As String
Dim TarifaIndividual As Currency
Dim TarifaDoble As Currency
Dim TarifaTriple As Currency
Dim TarifaCuadruple As Currency
Dim TarifaQuintuple As Currency
Dim TarifaNiño As Currency
Dim TarifaInfante As Currency
Dim Periodo As Variant
Dim RangoPeriodo As Variant
Dim xName As String
Hotel = Sheets(«CrearHotel»).Range(«B1»)
Habitación = Sheets(«CrearHotel»).Range(«B2»)
Desde = Sheets(«CrearPeriodo»).Range(«B2»)
Hasta = Sheets(«CrearPeriodo»).Range(«B3»)
TarifaIndividual = Sheets(«CrearPeriodo»).Range(«B4»)
TarifaDoble = Sheets(«CrearPeriodo»).Range(«B5»)
TarifaTriple = Sheets(«CrearPeriodo»).Range(«B6»)
TarifaCuadruple = Sheets(«CrearPeriodo»).Range(«B7»)
TarifaQuintuple = Sheets(«CrearPeriodo»).Range(«B8»)
TarifaNiño = Sheets(«CrearPeriodo»).Range(«B9»)
TarifaInfante = Sheets(«CrearPeriodo»).Range(«B10»)
Periodo = DateDiff(«d», Desde, Hasta) + 1
dias = DateDiff(«d», Desde, Hasta) + 1
xName = Sheets(«CrearPeriodo»).Range(«C1»)
ActiveWorkbook.Sheets(xName).Select
Rango = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
RangoPeriodo = ActiveSheet.Range(«A4»)
Range(«A4»).Select
Do While ActiveCell Empty
ActiveCell.Offset(1, 0).Select
Loop
With ActiveCell
Rango = .Resize(dias, 1).Address
.Value = Desde
.AutoFill Destination:=Range(Rango), Type:=xlFillSeries
End With
End Sub
Hola, muchas gracias por la información.
Me ha servido bastante, ahora estoy tratando de darle un nombre al rango definido en Excel para usarlo en una lista desplegable.
Alguna idea de cómo?
De antemano muchas gracias
Hola Diana.
Dependiendo del método que uses, tienes que pasar el rango con el método Names.Add
Un ejemplo es.
ThisWorkbook.Names.Add Name:=»Hola», RefersTo:=ThisWorkbook.Sheets(1).Range(«A1:A10»), Visible:=True
Saludos.
Gracias por responder, estoy utilizando el método 5, con el RefersTo:=NombredelRango me funciono perfecto!!!!
hola alguno me podria ayudar con esto:
Range(Cells(2, 1), Cells(sw, 6)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range(«$A$2:$F$5»), , xlYes).Name = _
«Tabla1»
quiero hacer que en lugar de F5 se la variable sw, que es el ultimo renglon con datos, no se si fui claro, me confunden los simbolos $, no se mucho programar
Hola tu método realmente me ha servido mucho, pero aún no logro crear la tabla, ya logré que excel seleccione el rango dinámico, pero cuando intento crear la tabla mediante:
ActiveSheet.ListObjects.Add(xlScrRange, Range(«StartCell, sht.Cells(LRow, LColumn)»,False,xlYes).Name=»Base» Me arroja error. ¿Podrías ayudarme con ello?
Hola Emmanuel.
Si pasas entre comillas tus variables como en Range(«StartCell, sht.Cells(LRow, LColumn)») estas convirtiendolas a texto, por lo que pierden su valor.
En su defecto seria:
ActiveSheet.ListObjects.Add(xlSrcRange, Range(StartCell, sht.Cells(LRow, LColumn)),False,xlYes).Name=»Base»
Saludos
las comillas eran mi problema, muchas gracias por este post, Saludos
Crear, mediante el uso de macros, tablas dinámicas con rangos variables *
Hola, me pareció muy bien explicado. Excelente contenido, felicidades. Quisiera saber si con esta forma de crear rangos variables, puedo usarla para crear tablas dinámicas con rangos variables y cómo podría usarla. Mil gracias, saludos.
Hola Karla.
En efecto es posible con estos métodos. Un ejemplo seria:
Sub tabla_dinamica()
Dim rng As Range, startCell As Range
Dim sht As Worksheet, pivotSht As Worksheet
Dim lRow As Long, lColumn As Long
Dim nombre as String
Set sht = ThisWorkbook.Sheets(«Tabla_Ventas»)
Set startCell = sht.Range(«A1»)
lRow = sht.Cells(sht.Rows.Count, startCell.Column).End(xlUp).Row
lColumn = sht.Cells(startCell.Row, sht.Columns.Count).End(xlToLeft).Column
nombre = «tablaDinamicaNueva»
Set rng = sht.Range(startCell, sht.Cells(lRow, lColumn))
Set pivotSht = Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
rng, Version:=7).CreatePivotTable TableDestination:= _
pivotSht.Range(«A3»), TableName:=nombre, DefaultVersion:=7
End Sub
Saludos.
Hola, buen día.
Acabo de llegar a la página y me parece muy interesante y muy bien explicado. Mi pregunta es si es posible hacer un rango dinámico con las filas de una tabla que cumplan cierta condición. Por ejemplo en una tabla de vendedores con su ubicación, salario, etc. y que haga un rango con todos los nombres que están en el norte. Cosa que si se elimina o se agrega un nuevo nombre, el rango se actualice solo. Finalmente ese rango lo usaría para una lista desplegable.
He visto otros artículos que me han dado nuevas ideas para otras aplicaciones.
Muchas gracias y saludos
Hola Alex.
Si es posible, podrías hacerlo dentro de la hoja con una macro de evento Changes, que evalue el rango segun los parametros de cambio.
Adicional, si tienes Office 365 puedes hacer todo esto que comentas con la función FILTRAR. Agregas los parámetros y a la hora de crear la validación la usas con referencia de desbordamiento.
Este artículo puede darte una idea.
https://excelcute.com/arrays-dinamicos-excel-365/
Saludos.
Hola que tal, mi nombre es Ricardo, encontré estos ejemplos que se me hacen muy buenos, estoy trabajando en un pequeño proyecto, donde tengo rangos dinámicos y con celdas en blanco y filtros, por lo cual el ejemplo dos se me hice muy bueno.
Encontré tus ejemplos buscando una respuesta, a ver si me ayudas, considerando que hay filtros para seleccionar por mes y la ultima celda fuera la «A20» y varias columnas, donde la fila 20 llevara subtotales y no todas las columnas.
La pregunta es puedo y como seleccionar la celda «A20» como Rango y considerando que puede ser dinámica esa celda.
Gracias, saludos
Hola Ricardo.
Que bueno saber que te sirvió.
Para tomar como parámetro la última celda de una tabla filtrada podrías hacer uso del método dos y guardar la celda a la que llegas en una variable.
Siguiendo el ejemplo de la entrada:
Dim uCelda as Range
Set uCelda = sh.Cells(sh.Rows.Count, 1).End(xlUp)
Cualquier duda escríbeme con más detalles a svallejo@excelcute.com