VBA excel macro to transpose a specific matrix


I'm new to VBA excel and I'm trying to code a macro that would convert a table like the following:

Time Station1 0 150.0 60 250.0 200 450.0 250 650.0 ... ...

into a transpose table like that with the values immediately following:

Time 0 60 200 250 ... Station1 150.0 250.0 450.0 650.0

So far I have been using some transpose function but I need to know the size in advance.


I was using the following code with the table in B2:C6

Sub Worksheet_Change() Set Target = ActiveCell Application.ScreenUpdating = False [B2:C6].Copy [E2].PasteSpecial Paste:=xlPasteValues, Transpose:=True Application.CutCopyMode = False Target.Select End Sub

It's more or less copying/pasting by hand..


When you are using Application.WorksheetFunction.Transpose(), you don't need to know the size of the variant array. However if you are referring to the Last used rows in the two columns, then give the following code a try.

In your case, you can try two variant arrays to get the <strong>multiple rows into multiple columns in one row</strong>.


Option Explicit Sub rowsToColumns() Dim vCol1 As Variant, vCol2 As Variant Dim lastRow As Long Dim WS As Worksheet Set WS = Sheets(2) lastRow = WS.Cells(WS.Rows.Count, "B").End(xlUp).Row '--since data starting with row 2 lastRow = lastRow - 1 vCol1 = WorksheetFunction.Transpose(WS.Range("B2").Resize(lastRow).Value) vCol2 = WorksheetFunction.Transpose(WS.Range("C2").Resize(lastRow).Value) '-- output to sheet WS.Range("B10").Resize(1, UBound(vCol1)) = vCol1 WS.Range("B11").Resize(1, UBound(vCol2)) = vCol2 End Sub

<strong>Sheet view:</strong>

<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/bS9xE.jpg" data-original="https://i.stack.imgur.com/bS9xE.jpg" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />


  • How to copy image from another sheet to active sheet
  • Worksheet_Change(Byval target as range) does not change the cells until I click the target range spe
  • Hide/Unhide cells with empty rows on Worksheet_Change
  • VBA excel macro to transpose a specific matrix
  • Use VBA in Google Sheets to auto [duplicate]
  • Run a Macro in Excel 2010 using Enter Key, Whether Worksheet has Changed or Not, when Pressed in Par
  • Allow cell population according to other cell's contents
  • VBA code to show Message Box popup if the formula in the target cell exceeds a certain value
  • Conditional Data Validation based on Dropdown List Response
  • dynamically changing the numbers in the cell based on the the user input
  • Two input cells equal to each other
  • Run a Macro every time sheet is changed
  • Excel VBA - Multiple cell update based on a change in ONE cell
  • Automatically creating worksheets based on a list in excel
  • Excel VBA onChange Event
  • Java Netty Unknown SSL content type error
  • Worksheet_Change doesn't fire up when streaming live data
  • How to make a cumulative sum in one cell?
  • How to get the exact text a user entered into a cell in excel
  • Animate svg path
  • hiding rows in excel based on words in a cell
  • Preventing Excel from updating the screen in C#
  • Compare two columns if another two columns are matching in excel macro
  • Equivalent to Application.ScreenUpdating Property in office-js Excel add-in
  • How to vectorize a for loop in R
  • Exporting data from Excel to Outlook
  • Aggregating based on “near” row values
  • Find and copy code
  • Check if one value in one column is in another column
  • WPF UI on multiple threads?
  • Original method still getting called in Moq even after CallBase = true/false
  • No error while instantiating abstract class, even though abstract method is not implemented
  • Most efficient way to move table rows from one table to another
  • std::remove_copy_if_ valgrind bytes in block are possibly lost in loss record
  • Functions in global context
  • XCode can't find symbols for a specific iOS library/framework project
  • Bitwise OR returns boolean when one of operands is nil
  • sending mail using smtp is too slow
  • costura.fody for a dll that references another dll
  • Binding checkboxes to object values in AngularJs