Assistance with converting a data range to keep initial column data but display the column data as rows against original line item

Question

Please can you assist with some excel VBA code. I found a similar question on StackOverflow (Transpose columns to rows keeping first 3 columns the same). However I am not able to get the result I am after.

I am looking to convert a data range to display column data against multiple instances of the original row but keep the original data in column A and then "transpose" the columns to rows against the same instance of the original row in columns B & C (for instance). The result can be in the same worksheet or in a new worksheet.

There can be varying numbers of rows and columns in the original dataset.

Example

Vincent Farcy · Accepted Answer · 2020-07-15 10:02:46Z

0

here's an example. is it ok for you ?

Sub convert()

    ' Variables
    Dim oWsFrom As Worksheet
    Dim oWsTo As Worksheet
    Dim oDataRange As Range
    Dim oCellRow As Range
    Dim oCellCol As Range
    Dim iRow As Integer
    
    ' Settings
    Set oWsFrom = ThisWorkbook.Worksheets("sheet1")
    Set oWsTo = ThisWorkbook.Worksheets("sheet2")
    oWsTo.Cells.ClearContents
    Set oDataRange = oWsFrom.Cells(1, 1).CurrentRegion
    
    ' Header
    oWsTo.Cells(1, 1).Value = "Tree"
    oWsTo.Cells(1, 2).Value = oWsFrom.Cells(1, 1).Value
    oWsTo.Cells(1, 3).Value = "Value"
    
    ' Body
    iRow = 1
    For Each oCellRow In oDataRange.Columns(1).Cells
        If oCellRow.Row > 1 Then
            For Each oCellCol In oDataRange.Rows(1).Cells
                If oCellCol.Column > 1 Then
                    iRow = iRow + 1
                    oWsTo.Cells(iRow, 1).Value = oCellRow.Value
                    oWsTo.Cells(iRow, 2).Value = oCellCol.Value
                    oWsTo.Cells(iRow, 3).Value = oWsFrom.Cells(oCellRow.Row, oCellCol.Column).Value
                End If
            Next
        End If
    Next

End Sub

edited Jul 15, 2020 at 10:02

answered Jul 15, 2020 at 9:57

Vincent Farcy

912 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SeanT Over a year ago

Thanks very much Vincent, this is exactly what I am after. Many thanks!

Collectives™ on Stack Overflow

Assistance with converting a data range to keep initial column data but display the column data as rows against original line item

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related