Question:

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.

[edit]

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>.

<strong>Code:</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" />