Step 5: Use on the given array to be able to transpose the list defined under Arr1 variable. Step 4: Now use the Range.Value method to define the range in which these values you wanted to get pasted. This would be the list you wanted to paste in your excel sheet.Īrr1 = Array('Lalit', 'Sneha', 'Ethyl', 'John', 'Cory') Step 3: Define the list as an array using the Array function.

Step 2: Define a new variable which can hold your one-dimensional array. Step 1: Insert a new module and define a new sub-procedure to create a macro in VBA. Let’s see how we can do this.įollow the below steps to use Transpose in VBA. Suppose you are working on a data with lists (which is a one-dimensional array) like names of an employee given (“Lalit”, “Sneha”, “Ethyl”, “John”, “Cory”) and you want this list to be pasted in excel cell wise. If you want to rotate your data frequently to view it from different angles, consider creating a PivotTable so that you can quickly pivot your data by dragging fields from the Rows area to the Columns area (or vice versa) in the PivotTable Field List.You can download this VBA Transpose Excel Template here – VBA Transpose Excel Template Example #1 – VBA Transpose of One-dimensional Array Verify these formulas use absolute references-if they don’t, you can switch between relative, absolute, and mixed references before you rotate the data. If your data includes formulas, Excel automatically updates them to match the new placement.

Īfter rotating the data successfully, you can delete the original table and the data in the new table will remain intact. Right-click over the top-left cell of where you want to paste the transposed table, then choose Transpose. The new table that you paste there will entirely overwrite any data / formatting that’s already there. Note: Ensure that you copy the data to do this, since using the Cut command or Ctrl+X won’t work.Ĭhoose a new location in the worksheet where you want to paste the transposed table, ensuring that there is plenty of room to paste your data.

Where is transpose button in excel