Arrays VBA para convertir columna de datos en tabla

Los arrays VBA nos permiten organizar los datos que aparecen en una columna en forma de tabla.

En el post De datos en una columna a tabla hemos visto cómo organizar los datos de una columna en una tabla usando funciones y fórmulas.

Ahora resolveremos el problema con otro enfoque. Por tanto, obtenemos el mismo resultado que con las fórmulas, pero con una técnica distinta: arrays VBA.

Los arrays VBA son una parte muy importante del lenguaje de programación de Excel. Un ejemplo sencillo de array es la lista de meses del año o una lista que resuma las ventas totales de cada mes.

Sub ejemploArray()
Dim miSemana As Variant
     miSemana = Array("Lunes", "Martes", "Miércoles", "Jueves", "Viernes")
     Debug.Print miSemana(0)
     Debug.Print miSemana(2)
     Debug.Print miSemana(4)
End Sub

¿Qué es un array?

Los arrays VBA son un tipo de variable. Lo usamos para almacenar una lista de datos del mismo tipo. En cierto modo podemos definirlo como un almacén temporal de datos.

más info en: Arrays · Lo básico

Y puesto que trabajan como listas, resultan muy útiles para manejar los valores de las celdas de una hoja de cálculo por dos buenas razones:
primero, porque un array VBA es una estructura de datos muy flexible,
y segundo, porque podemos procesar esos datos (para hacer cálculos, ordenar valores, eliminar elementos…) antes de volcarlos sin alterar las celdas de origen.

Cómo convertimos los datos de una columna en tabla

Así que tenemos apilados los datos de una lista de productos en una columna, y queremos convertir la columna en una tabla.

En la columna, hay cuatro tipos de datos que se repiten hacia abajo y que se corresponden con cuatro campos.

Lo que pretendemos es muy sencillo: en primer lugar que cada celda de la tabla albergue el valor que le corresponda de la columna de datos, y después que los valores se ordenen según los campos a los que pertenecen.

Paso 1: insertamos un nuevo módulo

Sub DatosColumnaATabla()

End Sub

Paso 2: declaramos las variables

Empezamos declarando las variables:

Sub DatosColumnaATabla()
' Declaramos las variables
     Dim i, j As Integer
     Dim primeraFila, ultimaFila As Integer
     Dim contadorArray As Integer
     Dim arrayColumna() As Variant
 End Sub

Las variables i y j serán contadores para los bucles FOR … NEXT.

La variable primeraFila recorrerá la columna de datos para incluir los valores de sus celdas en nuestro array.
En este caso, como es un rango pequeño, podríamos saber cuál es el tamaño del rango con echar un vistazo hacia abajo en la hoja de cálculo.

Sin embargo, cuando el rango de la columna tiene un número impreciso o muy enorme de celdas, mover la barra es una mala práctica para conocer el tamaño del rango.

Con la variable ultimaFila sabemos cuál es la última fila de la columna de datos para, a partir de ahí, dar tamaño al array.

La variable contadorArray llenará las celdas destino con los valores del array.

La variable arrayColumna será nuestro array.
Como no sabemos el número de elementos que contendrá, no le damos tamaño y lo declaramos vacío.
El tamaño del array depende del número de celdas de la columna y no siempre sabremos cuántas son.
Por lo tanto, primero nos vamos a asegurar el número exacto de elementos que contendrá con la variable ultimaFila. En otro caso, obtendríamos un error.

Paso 3: valor de la última fila

Damos valor a la variable ultimaFila:

Sub DatosColumnaATabla()
' Declaramos las variables
     Dim i, j As Integer
     Dim primeraFila, ultimaFila As Integer
     Dim contadorArray As Integer
     Dim arrayColumna() As Variant
' Valor de la variable ultimaFila
     ultimaFila = Cells(3, 2).End(xlDown).Row
End Sub

Cells(3, 2) es la celda donde arranca el rango, B3: fila 3, columna 2.

End(xlDown).Row nos lleva hasta la última celda de la columna 2 (B). Así sabremos cuántas filas tiene.

Paso 4: dimensionamos el array

Ahora tenemos que dar tamaño al array.
La cantidad de celdas de la columna de datos nos da el número de elementos del array, es decir, su tamaño.

Con esta técnica, el rango de la columna de datos es “B3:BultimaFila”, donde ultimaFila es el valor de la variable.
ultimaFila nos devuelve el valor de la última fila del rango, pero no es el número de celdas del rango. Por lo tanto, el número de celdas del rango es el valor de ultimaFila – 3, porque 3 es la celda donde arranca el rango.
Ahora ya podemos dar tamaño al rango con la sentencia REDIM.

REDIM es la sentencia que nos permite dimensionar un array cuando no hemos declarado inicialmente su tamaño.

Sub DatosColumnaATabla()
' Declaramos las variables
      Dim i, j As Integer
      Dim primeraFila, ultimaFila As Integer
      Dim contadorArray As Integer
      Dim arrayColumna() As Variant
' Valor de la variable ultimaFila
      ultimaFila = Cells(3, 2).End(xlDown).Row
' Damos tamaño al array con ReDim
      ReDim arrayColumna(ultimaFila - 3)
 End Sub

Si supiéramos el tamaño del array escribiríamos REDIM array(n), donde n es el número de elementos que tendrá el array.

Paso 5: poblamos el array

Ahora que el array ya tiene tamaño, vamos a poblarlo con los elementos de la columna de datos. Para hacerlo, usamos un bucle FOR … NEXT que recorre la columna, celda a celda, desde la primera a la última.

Preparamos el bucle. Como la primera celda está en la fila 3, damos valor a la variable.

' Damos valor a la variable primeraFila
     primeraFila = 3

En cada iteración, el bucle tomará el valor de la celda y lo añadirá como elemento al array. Después, pasará a la siguiente fila y repetirá la acción. Cuando llegue a la última celda se detendrá.

' Bucle que puebla el array
     For i = LBound(arrayColumna) To UBound(arrayColumna)
         arrayColumna(i) = Cells(primeraFila, 2).Value
         primeraFila = primeraFila + 1
     Next i

LBound y UBound son los límites inferior y superior del array.

más info en: Funciones LBound y UBound para arrays

Con esta línea

arrayColumna(i) = Cells(primeraFila, 2).Value

el bucle arranca en la primera fila y asigna a cada elemento del array el valor que le toca.

Tras añadir ese valor de la celda al array, el bucle avanza una celda hacia abajo con la sentencia:

primeraFila = primeraFila + 1 

Cuando alcanza la ultima celda, el procedimeinto abandona el bucle FOR … NEXT

El código hasta este punto:

Sub DatosColumnaATabla()
' Declaramos las variables
     Dim i, j As Integer
     Dim primeraFila, ultimaFila As Integer
     Dim contadorArray As Integer
     Dim arrayColumna() As Variant
' Valor de la variable ultimafila
     ultimaFila = Cells(3, 2).End(xlDown).Row
' Damos tamaño al array con ReDim
     ReDim arrayColumna(ultimaFila - 3)
' Damos valor a la variable primeraFila
     primeraFila = 3
' Bucle que puebla el array
     For i = LBound(arrayColumna) To UBound(arrayColumna)
         arrayColumna(i) = Cells(primeraFila, 2).Value
         primeraFila = primeraFila + 1
     Next i
 End Sub

Paso 6: escribimos en la tabla con arrays VBA

Una vez poblado el array con los valores de la columna de datos, los escribimos en las celdas de la tabla.

La tabla es una matriz de dos dimensiones: filas y columnas. Para recorrerla tenemos que usar dos bucles FOR … NEXT. Uno primero para recorrer las filas de la tabla y el segundo bucle FOR … NEXT, anidado dentro del primero, para recorrer las columnas.

El esquema del bloque de código es el siguiente:

For recorremos filas
     For recorremos columnas
     Salimos de las columnas
Salimos de las filas

Recorremos, uno a uno, los elementos del array con la otra variable que usamos como contador: contadorArray.
Esta variable la iniciamos en 0.

contadorArray = 0 

El límite superior del array es múltiplo de 4, ya que cuatro son los campos de la tabla. Así que tenemos que dividir el valor de cada elemento del array entre cuatro para que el bucle pueda avanzar a la siguiente fila:

contadorArray = 0 
For i = LBound(arrayColumna) To (UBound(arrayColumna) / 4)     
 
Next i

En las celdas, se escribe el valor que les corresponde.

     For j = 4 To 7
         Cells(i + 4, j).Value = arrayColumna(contadorArray)
         contadorArray = contadorArray + 1         
     Next j

El valor de j (las columnas de la tabla) se ajusta según nuestras necesidades. En este caso, las columnas van desde la 4 hasta la 7.

Después el valor de fila (variable i). Ajustamos el valor de la celda de inicio de la tabla según nos convenga. En nuestro caso, está en la fila 4.

Este es el bloque de código que necesitamos para escribir en las celdas de la tabla:

contadorArray = 0
For i = LBound(arrayColumna) To (UBound(arrayColumna) / 4)     
     For j = 4 To 7
         Cells(i + 4, j).Value = arrayColumna(contadorArray)
         contadorArray = contadorArray + 1         
     Next j 
Next i

Ejecutamos con F5 para la tabla con los valores de la columna.

imagen

Este es el código hasta este punto

For i = LBound(arrayColumna) To (UBound(arrayColumna) / 4)     
     For j = 4 To 7
         Cells(i + 4, j).Value = arrayColumna(contadorArray)
         contadorArray = contadorArray + 1         
     Next j 
Next i

Ya solo tenemos que darle formato. Nuestro siguiente paso.

Paso 7: damos formato a la tabla

Para terminar damos formato a los bordes de las celdas y así tener una apariencia uniforme.

Usamos un bloque WITH … END para asignar las propiedades a los bordes de la tabla, dentro del segundo bucle FOR … NEXT.

For recorremos filas
     For recorremos columnas
         With elemento
         Salimos de with
     Salimos de las columnas
Salimos de las filas

Para cada celda aplicamos un estilo de línea contínua con

.LineStyle = XlLineStyle.xlContinuous

Damos un tamaño de 2 al grosor de la línea:

.Weight = 2

y un color:

.Color = RGB(0, 112, 192)

Y ya hemos dado a la tabla un formato muy legible.

       ' Damos formato a las celdas de la tabla
       With Cells(i + 4, j).Borders
             .LineStyle = XlLineStyle.xlContinuous
             .Weight = 2
             .Color = RGB(0, 112, 192)
       End With

Código del procedimiento

Sub DatosColumnaATabla()
' Declaramos las variables
     Dim i, j As Integer
     Dim primeraFila, ultimaFila As Integer
     Dim contadorArray As Integer
     Dim arrayColumna() As Variant
' Valor de la variable ultimafila
     ultimaFila = Cells(3, 2).End(xlDown).Row
' Damos tamaño al array con ReDim
     ReDim arrayColumna(ultimaFila - 3)
' Damos valor a la variable primeraFila
     primeraFila = 3
' Bucle que puebla el array
     For i = LBound(arrayColumna) To UBound(arrayColumna)
         arrayColumna(i) = Cells(primeraFila, 2).Value
         primeraFila = primeraFila + 1
     Next i
' Escribimos los valores del array en las celdas de la tabla
     contadorArray = 0
     For i = LBound(arrayColumna) To (UBound(arrayColumna) / 4)
          For j = 4 To 7
              Cells(i + 4, j).Value = arrayColumna(contadorArray)
              contadorArray = contadorArray + 1
         ' Damos formato a las celdas de la tabla
              With Cells(i + 4, j).Borders
                  .LineStyle = XlLineStyle.xlContinuous
                  .Weight = 2
                  .Color = RGB(0, 112, 192)
              End With
          Next j
     Next i
 End Sub

Descarga el libro de trabajo

Libro de trabajo con datos y código Este es un archivo con extensión .xlsm. Comprueba que el navegador no cambia la extensión al archivo.

www.maguu.es