CODEWITHSUNDEEP
excelvba
Eirik Christoffer Andersen
Eirik Christoffer Andersen

Reputation: 1

Moving data in excel rows multiple under single column

I am having some problems with moving multiple data rows under one column.

How it currently is

enter image description here

And this is how i would like it

enter image description here

I have tryed to find a solution to this problem for some time now, but i can't seem to find anything that does this.

Thank you in advance

Upvotes: 0

Views: 591

Answers (2)

NickSlash
NickSlash

Reputation: 5077

Was thinking of a way to do it with worksheet functions but came up blank. Using VBA is probably a lot simpler.

This is a fairly simple way to do it. It Looks at Sheet1 and outputs to Sheet2 (as it would overwrite some values if you tried to output to the same sheet without caching all the values first).

This works due to how the Sheet.Cells Object(?) stores data, which is A1,B1..Y1,Z1,A2,B2..Y2,Z2 where Y and Z last cells in the row. (the opposite of your result basically)

Sub Main()
Dim S1 As Worksheet
Dim S2 As Worksheet
Dim Cell As Variant
Dim Row As Long

    Set S1 = ThisWorkbook.Worksheets("Sheet1") ' Source Sheet
    Set S2 = ThisWorkbook.Worksheets("Sheet2") ' Destination Sheet

    Row = 1 ' Start Output on this row

    For Each Cell In S1.UsedRange.Cells

        S2.Cells(Row, 1).Value = Cell.Value
        Row = Row + 1

    Next Cell

End Sub

Upvotes: 0

Neil Mussett
Neil Mussett

Reputation: 708

Paste Special, Transpose is the only thing that comes close to what you want, but that won't do it for you. I think you are going to have to write a macro or do it manually.

Upvotes: 1

Related Questions