Propiedad Resize para manejar rangos en Excel

Puede descargar el archivo resize.xlsm

Cuando programamos macros en Excel usando VBA es muy habitual trabajar con rangos. Una propiedad de los rangos que modifica su tamaño es resize.

Rango.Resize(filas, columnas).Select

Veamos algunos ejemplos de su uso.

Resize1

Da color aleatorio a las celdas del rango B4:E8.

Sub Resize1()
Dim rojo As Byte, verde As Byte, azul As Byte
Dim R As Range
Set R = Range("B4")
Range("A1").Select
Randomize 'elegimos colores aleatorios
rojo = Int(Rnd() * 100) + 100
verde = Int(Rnd() * 100) + 100
azul = Int(Rnd() * 100) + 100
R.Resize(5, 4).Interior.Color = RGB(rojo, verde, azul)
End Sub

Observe que después de Resize(5.4) se ha de poner algo, o bien, .Select para seleccionar, o bien, .Interior.Color=RGB(rojo, verde, azul) como en este caso. Pero si después de Resize se deja sin poner nada nos dará error.

Resize2

Permite seleccionar un rango de 3 filas y 2 columnas usando como celda de origen la B4.

Range("B4").Resize(3, 2).Select

Sub Resize2()
'Seleccionamos un rango de 3 filas y 2 columnas
'La celda de origen es B4
Range("B4").Resize(3, 2).Select
End Sub

Resize3

Permite seleccionar un rango de 3 filas en la primera columna usando como celda de origen la B4.

Range("B4").Resize(3).Select

Sub Resize3()
'Seleccionamos un rango de 3 filas de la primera columna
'La celda de origen es B4
Range("B4").Resize(3).Select
End Sub

Resize4

Permite seleccionar un rango de 3 columnas en la primera fila usando como celda de origen la B4.

Range("B4").Resize(, 3).Select

Sub Resize4()
'Seleccionaremos un rango de 3 columnas de la primera fila
'La celda de origen es B4
Range("B4").Resize(, 3).Select
End Sub

Resize5

Permite seleccionar un rango de 2 columnas en la primera fila usando como celda de origen la B4 y hasta la fila 8, ya que el rango indicado es hasta la E8.

Range("B4:E8").Resize(, 2).Select

Sub Resize5()
Range("B4:E8").Resize(, 2).Select
End Sub

Resize6

Permite seleccionar un rango de 3 columnas en la primera fila usando como celda de origen la A4.

Range("B4").Offset(, -1).Resize(, 3).Select

Es una maravilla poder usar Offset para cambiar el rango de referencia, y usar, como en el ejemplo, valores negativos. Esto aporta flexibilidad al manejar gran cantidad de rangos cambiantes.

Sub Resize6()
Range("B4").Offset(, -1).Resize(, 3).Select
End Sub

Resize7

Copia el rango amarillo (B11:E15) en el rango superior (B4:E8).

Range("B4").Resize(5, 4) = A

Este es un uso muy útil de la propiedad Resize ya que nos permite depositar (imprimir) de golpe todo un rango o el contenido de una matriz.

Sub Resize7()
Dim A As Variant
Worksheets("Hoja1").Range("A1").Select
A = Range("B11:E15")
Range("B4").Resize(5, 4) = A
End Sub

Resize8

Hace lo mismo que la macro anterior, copia el rango amarillo (B11:E15) en el rango superior (B4:E8).

Range("B4").Resize(R.Rows.Count, R.Columns.Count) = A

Este procedimiento realiza el mismo trabajo que el anterior aunque lo hemos programado de otra forma. En este caso, creamos las variables n y m que calculan el número de filas y columnas respectivamente del rango R. De esta forma hacemos más flexible el código ya que evitamos tener que dar nosotros los parámetros a la propiedad Resize.

Sub Resize8()
Dim A As Variant
Dim R As Range
Worksheets("Hoja1").Range("A1").Select
Set R = Range("B11:E15")
A = R
n = R.Rows.Count
m = R.Columns.Count
Range("B4").Resize(n, m) = A
End Sub

Resize9

Genera una matriz de números aleatorios y la imprime en una posición inicial variable y con un tamaño variable.

Range(Cells(pf, pc), Cells(pf, pc)) .Resize(n, m) = A

Seguidamente se muestra el código de ejemplo utilizado para ilustrar la versatilidad de la propiedad Resize.

Sub Resize9()
Dim A As Variant
Dim R As Range
Dim tf As Byte, tc As Byte, pf As Byte, pc As Byte
Worksheets("Hoja2").Activate
Range("E7:AQ45").Clear
Randomize
Range("A1").Select
tf = Int(Rnd() * 20) + 1 'tamaño:fila
tc = Int(Rnd() * 20) + 1 'tamaño: columna
pf = Int(Rnd() * 20) + 7 'posición inicial: fila
pc = Int(Rnd() * 20) + 5 'posición inicial: columna
[C6] = tf
[D6] = tc
[C5] = pf
[D5] = pc
Set R = Range(Cells(pf, pc), Cells(pf + tf - 1, pc + tc - 1))
R.Interior.Color = RGB(0, 255, 100)
A = R
n = R.Rows.Count
m = R.Columns.Count
ReDim A(n, m)
For i = 1 To n
  For j = 1 To m
    A(i, j) = Int(Rnd() * 100)
  Next j
Next i
Range(Cells(pf, pc), Cells(pf, pc)).Resize(n, m) = A
End Sub

Veamos un GIF animado donde se aprecia que el rango varía en posición y tamaño de forma aleatoria.

Conviene ver cómo se resolvió en otros casos la necesidad de imprimir de golpe toda una matriz, incluso de gran tamaño. Recomiendo ver los siguientes enlaces a otros post publicados.

• Transferir los datos de un Rango a un Array
• Ordenar un millón de valores

Promedio condicional

Puede descargar el archivo de Excel siguiente.

• promedio_condicional.xlsx

Disponemos en Excel de las funciones siguientes.

• SUMAR.SI
• SUMAR.SI.CONJUNTO
• CONTAR.SI
• CONTAR.SI.CONJUNTO

Pero no disponemos de la función PROMEDIO.SI

Vamos a ver un caso donde realizamos un promedio condicional donde varía el rango que deseamos promediar y además eliminamos los valores que no son numéricos. Lo vamos a resolver por tres métodos.

Deseamos calcular el promedio anual del Euribor a un año. La información la obtenemos del Banco de España.

• Tipos de interés y tipos de cambio

En el punto 1.7 disponemos de un histórico de Series Temporales, concretamente en el siguiente enlace podremos descargar el archivo csv.

• Series temporales del cuadro TI.1.7

Método 1

Consiste en crear cada rango de forma manual. Para ayudarnos creamos la columna E.

Método 2

Sin usar fórmulas matriciales podemos obtener el promedio usando SUMAR.SI y CONTAR.SI.CONJUNTO.

Veamos la celda I6.
=SUMAR.SI(Años;G6;Porcentaje)/CONTAR.SI.CONJUNTO(Años;G6;Porcentaje;">0")/100

Método 3

En este caso usamos una función matricial. Recuerde que este tipo de funciones se validan no pulsando ENTER, sino pulsando simultáneamente tres teclas: CONTRO+SHIFT+ENTER.

La celda J6 contiene la siguiente expresión.
=SUMA((--(Años=G6))*SI.ERROR(VALOR(Porcentaje);0))/SUMA((Años=G6)*ESNUMERO(Porcentaje))/100

Para saber más ...

En el siguiente post se resuelve un caso similar pero que se caracteriza porque los rangos son todos del mismo tamaño. Lo interesante del caso que hemos visto en esta ocasión es que los rangos no son todos iguales, ya que existen años con 365 días y años con 366 días. Además teníamos el handicap de contar con días en los que no existía Euribor y se marcaban con un guión bajo (_).

• Media Móvil a intervalos fijos

Trabajar con Nombres de Rango

Descargar el archivo excelavanzado_nombres_de_rango.xlsx

Veamos cómo podemos trabajar con nombres de rango.

• Creación de nombres de rango. No valen espacios en el nombre
• Uso de los nombres de rango en las fórmulas
• 'Crear desde la selección' permite crear rangos usando los indicadores de cabecera de fila y columna de una tabla.
• Uso de la función INDIRECTO
• No es necesario usar las funciones DESREF o INDICE para encontrar valores dentro de una tabla indicando la fila y la columna que corresponde a esa celda. Esto se puede hacer de forma sencilla si hemos nombrado rangos.

Primer vídeo

Segundo vídeo

Podemos ver un post anterior donde se explica cómo borrar nombres de rango. Es el siguiente.

• Borrar o Editar un Nombre de Rango

También se recomienda la lectura de la siguientes post que son de un nivel algo mayor.

• Rango Dinámico
• Seleccinar datos con un desplegable

Borrar o Editar un Nombre de Rango

Pregunta

Si le doy un nombre a un conjunto de celdas (tabla) y veo que me he equivocado, ¿cómo puedo quitar el nombre a esa tabla?. Supongamos que ya he guardado el fichero y no puedo deshacer la operación.

Respuesta

Si tienes la versión 2007, para gestionar los nombres de rango debes ir a:

1. Fórmulas
2. Administrador de nombres
3. Donde tienes las opciones: Nuevo, Editar y Eliminar

Si tienes la versión de Excel 2003 o anteriores, puedes llegar a una ventana similar a ésta, donde podrás gestionar los nombres de rango. Está en: Insertar, Nombre, Definir.