CODEWITHSUNDEEP
vbaexcel
Nicholas
Nicholas

Reputation: 2668

How do I transform a matrix of data on the spreadsheet into a single column using VBA?

Say, my original data block is worksheets(1).range("A1:C100"), and I'd like to stack the columns of this block into a single column, that is, I first put first column, then the second column goes below, and finally the third column. In the end, I should have a single column, say being put in worksheets(2).range("A1:A300"). I wonder if there's any smart and fast algorithm to achieve this?

Upvotes: 0

Views: 51

Answers (3)

Gary's Student
Gary's Student

Reputation: 96753

Without VBA, In Sheet2 cell A1 enter:

=OFFSET(Sheet1!$A$1,MOD(ROWS($1:1)-1,100),ROUNDUP(ROWS($1:1)/100,0)-1,)

and copy down.

and with VBA

Sub copy_table_to_column()
    Dim s As String

    s = "=OFFSET(Sheet1!$A$1,MOD(ROWS($1:1)-1,100),ROUNDUP(ROWS($1:1)/100,0)-1,)"

    With Worksheets("Sheet2").Range("A1:A300")
        .Formula = s
        .Value = .Value
    End With
End Sub

Upvotes: 1

PaulG
PaulG

Reputation: 1189

This might be good enough for you... Hope it helps.

Option Explicit

'Define the test function...
Sub test()

Dim vData As Variant
Dim r As Range

Set r = Sheet1.Range("A1:C100")
vData = ConcatinateColumns(r)

End Sub

'Define the function to concatinate columns.
Public Function ConcatinateColumns(ByVal Data As Range)

Dim vTemp As Variant
Dim i As Integer, j As Long, k As Long

'Get the data for each cell to a variant.
vTemp = Data.Value
ReDim vData(1 To (UBound(vTemp, 1) - LBound(vTemp, 1) + 1) * (UBound(vTemp, 2) - LBound(vTemp, 2) + 1), 1 To 1) As Variant

For i = LBound(vTemp, 2) To UBound(vTemp, 2)
    For j = LBound(vTemp, 1) To UBound(vTemp, 1)
        k = k + 1
        vData(k, LBound(vData, 1)) = vTemp(j, i)
    Next
Next
ConcatinateColumns = vData

End Function

Upvotes: 0

mjsqu
mjsqu

Reputation: 5452

There might be a better way, but I usually do it with an Offset

I=0

For Each A in Worksheets(1).Range("A1:A100").Cells
  Worksheets(2).Range("A1").Offset(I,0) = A.Value
  I = I + 1
Next
For Each B in Worksheets(1).Range("B1:B100").Cells
  Worksheets(2).Range("A1").Offset(I,0) = B.Value
  I = I + 1
Next
For Each C in Worksheets(1).Range("C1:C100").Cells
  Worksheets(2).Range("A1").Offset(I,0) = C.Value
  I = I + 1
Next

Upvotes: 1

Related Questions