Transposición de VBA

Al ser un usuario de Microsoft Excel, es posible que haya utilizado con frecuencia la función de transposición que permite el intercambio entre filas y columnas para una matriz. Convertir filas en columnas y columnas en filas es lo que hace una función de transposición en Excel. El número de filas se convierte en un número de columnas y viceversa. Significa que si tiene 2 filas y 3 columnas en su matriz, después de la transposición cambiará a una matriz con 3 filas y 2 columnas. En este tutorial, veremos la Transposición de VBA que le permitirá automatizar el método de transposición que usará en Excel.

Sintaxis de transposición en Excel VBA

VBA Transpose tiene la siguiente sintaxis:

Dónde,

Arg1 : es un argumento obligatorio que no es más que un rango de celdas que queríamos transponer (es decir, matriz).

La primera parte de la sintaxis no es más que una expresión bajo la cual se puede utilizar la función Transponer. Por ejemplo, WorksheetFunction.

¿Cómo utilizar Excel VBA Transpose?

Aprenderemos cómo usar una Transposición con algunos ejemplos en Excel VBA.

Puede descargar esta plantilla de Excel de transposición de VBA aquí - Plantilla de Excel de transposición de VBA

Ejemplo # 1 - Transposición VBA de matriz unidimensional

Suponga que está trabajando en datos con listas (que es una matriz unidimensional) como los nombres de un empleado ("Lalit", "Sneha", "Ethyl", "John", "Cory") y desea esta lista para ser pegado en excel cell cell. Veamos cómo podemos hacer esto.

Siga los pasos a continuación para usar Transpose en VBA.

Paso 1: inserte un nuevo módulo y defina un nuevo subprocedimiento para crear una macro en VBA.

Código:

 Sub Trans_ex1 () End Sub

Paso 2: defina una nueva variable que pueda contener su matriz unidimensional.

Código:

 Sub Trans_ex1 () Dim Arr1 As Variant End Sub

Paso 3: Defina la lista como una matriz usando la función Array. Esta sería la lista que desea pegar en su hoja de Excel.

Código:

 Sub Trans_ex1 () Dim Arr1 como variante Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") End Sub

Paso 4: Ahora use el método Range.Value para definir el rango en el que desea pegar estos valores.

Código:

 Sub Trans_ex1 () Dim Arr1 como variante Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") Rango ("A1: A5"). Valor = End Sub

Paso 5: Use Application.WorksheetFunction.Transpose en la matriz dada para poder transponer la lista definida en la variable Arr1.

Código:

 Sub Trans_ex1 () Dim Arr1 As Variant Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") Range ("A1: A5"). Value = Application.WorksheetFunction.Transpose (Arr1 ) Fin Sub

Aquí, hemos definido un código que permitirá al sistema transponer los datos en una matriz de lista llamada Arr1 y luego almacenarlos en la celda A1: A5 en la hoja de trabajo activa.

Paso 6: presione F5 o el botón Ejecutar debajo de VBE para ejecutar este código y ver la salida en la hoja de cálculo de Excel activa.

Puede ver que la matriz dada de la lista se transpone en una sola columna y se almacena en la celda A1 a A5.

Ejemplo # 2 - Transposición VBA de matriz bidimensional

Suponga que tiene una matriz bidimensional de empleados y sus salarios como se muestra a continuación:

Estos son datos bidimensionales con seis filas y dos columnas. Después de la transposición, sería una matriz con dos filas y seis columnas.

Siga los pasos a continuación para usar Transpose en VBA.

Paso 1: defina un subprocedimiento para almacenar una macro.

Código:

 Sub Trans_Ex2 () End Sub

Paso 2: decida el rango en el que desea transponer estos datos. Se puede hacer usando la función Sheets.Range.Value. Estaría guardando la matriz transpuesta en la celda D1 a I2.

Código:

 Sub Trans_Ex2 () Hojas ("Ejemplo # 2"). Rango ("D1: I2"). Valor = Fin Sub

Paso 3: Use WorksheetFunction.Transpose para poder asignar una matriz A1: B6 para transponer la función.

Código:

 Sub Trans_Ex2 () Hojas ("Ejemplo # 2"). Rango ("D1: I2"). Valor = WorksheetFunction.Transpose (End Sub

Paso 4: Necesitamos especificar el argumento para la función Transponer. Queríamos transponer un rango de matriz A1: B6. Por lo tanto, use Range ("A1: B6") como argumento.

Código:

 Sub Trans_Ex2 () Hojas ("Ejemplo # 2"). Rango ("D1: I2"). Valor = WorksheetFunction.Transpose (Rango ("A1: B6")) Fin Sub

Paso 5: presiona F5 o el botón Ejecutar para ejecutar este código y ver el resultado.

Aquí, el rango de la matriz A1: B6 se transpone y se almacena en un rango de la matriz D1: I2 con la ayuda de la función VBA Transpose en excel.

Ejemplo # 3 - Transposición de VBA de una matriz con Método especial de pegado

También podemos transponer la matriz y pegar tan especial como en Excel usando Alt + E + S. Podemos usar diferentes operaciones bajo este método especial de pegar.

Consideremos los mismos datos que hemos usado en el ejemplo anterior.

Siga los pasos a continuación para usar Transpose en VBA.

Paso 1: defina un subprocedimiento para almacenar la macro.

Código:

 Sub Trans_Ex3 () End Sub

Paso 2: defina dos nuevas variables, una que pueda contener la matriz de origen de datos (sourceRng) y otras que puedan contener el rango de matriz de salida (targetRng).

Código:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range End Sub

Tenga en cuenta que el tipo de estas variables se define como (Excel.Range). Porque queríamos transponer los datos, que es un rango de matriz.

Paso 3: establezca el rango de origen como A1: B6 (los datos de los que queríamos transponer) utilizando la función Sheets.Range.

Código:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Establecer sourceRng = Sheets ("Ejemplo # 3"). Range ("A1: B6") End Sub

Paso 4: Establezca el rango de destino / destino como D1: I2 (El rango de celdas donde se almacenará la salida) usando la función Sheets.Range.

Código:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Set sourceRng = Sheets ("Example # 3"). Range ("A1: B6") Set targetRng = Sheets ("Example # 3"). Range ( "D1: I2") Fin Sub

Paso 5: Ahora, use el comando Copiar para copiar el rango de datos de origen de su hoja de trabajo.

Código:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Set sourceRng = Sheets ("Example # 3"). Range ("A1: B6") Set targetRng = Sheets ("Example # 3"). Range ( "D1: I2") sourceRng.Copy End Sub

Paso 6: Vamos a usar la función PasteSpecial en la variable targetRng para guardar la salida transpuesta dentro del rango objetivo (D1: I2) en su hoja de trabajo.

Código:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Set sourceRng = Sheets ("Example # 3"). Range ("A1: B6") Set targetRng = Sheets ("Example # 3"). Range ( "D1: I2") sourceRng.Copy targetRng.PasteSpecial Paste: = xlPasteValues, Operation: = xlNone, SkipBlanks: = False, Transpose: = True End Sub

Pegar : permite pegar valores en un formato diferente (como Pegar como valores, como Fórmulas, Como formato, etc.). Es similar al de Excel (hacemos Alt + E + S para tener diferentes opciones especiales de pegado). En nuestro ejemplo, lo hemos configurado para pegar como valores.
Operación : Existen diferentes operaciones que se pueden realizar como sumar, restar, multiplicar, dividir (de la misma manera que podemos hacerlo en Excel).
SkipBlanks : si se establece True, este comando permite omitir los espacios en blanco, si los hay, de sus datos mientras realiza diferentes operaciones especiales de pegado. Lo configuramos en False, significa que no queríamos omitir los espacios en blanco.
Transposición : si se establece en Verdadero, permite tomar la transposición de una matriz de datos.

Paso 7: Ejecute este código presionando F5 o el botón Ejecutar y vea la salida.

Cosas para recordar

Mientras trabaja en una matriz unidimensional, siempre debe ser horizontal (una fila, varias columnas) para aplicar el método de transposición.
Es obligatorio averiguar el número de filas y el número de columnas mientras se usa VBA Transpose en Excel. Si tenemos 3 filas y 5 columnas, después de la transposición habría 5 filas con tres columnas.
Normalmente, el método de transposición no incluye el formato de los datos de origen. Si desea aplicar el mismo formato que el de los datos de origen, debe configurarlo manualmente o, si usa la opción especial XlPasteFormat de pegar, podrá mantener el formato de los datos de origen en los datos de destino.
El número de elementos que la función Transposición puede tener en una matriz no puede exceder 5461.
Una matriz no puede contener ningún elemento / cadena que tenga una longitud superior a 255. Si se incluye, causará errores como 13, No coinciden los tipos, 5, Llamada o argumento de procedimiento no válido, 1004, error definido por la aplicación o por objeto .
La matriz de origen no puede contener ningún valor nulo (como "Nulo", "# N / A").

Artículos recomendados

Esta ha sido una guía para la Transposición de VBA. Aquí discutimos cómo usar Excel VBA Transpose junto con ejemplos prácticos y una plantilla de Excel descargable. También puede consultar nuestros otros artículos sugeridos:

Guía para la función de cadena VBA
Fórmula TRANSPOSE Excel
Aprenda el caso de VBA en Excel
Eliminar (eliminar) filas en blanco en Excel

Transposición de VBA - ¿Cómo utilizar Excel VBA Transpose?

Tabla de contenido: