Good Morning,
I need to parse huge volumes of data that looks like the following and need to run through multiple columns and put it in 1 column.
the data looks as follows.
Col1 Col2 Col3 Col4 Col5 Co6 Col 7
a b C d 1 2 3
e f g h 4 5 6
The output post macro, need to look like follow.
Col1 Col2 Col3 Col4 Col5 Co6 Col 7
a b C d 1
a b C d 2
a b C d 3
e f g h 4
e f g h 5
e f g h 6
My Macro partially works could anyone assist in with what I'm missing for it to work.
The output I seem to get now is:
a b C d a
a b C d b
a b C d c
a b C d d
a b C d 1
a b C d 2
Sub Transform()
Dim targetRowNumber As Long
targetRowNumber = Selection.Rows(Selection.Rows.Count).Row + 2
Dim col1 As Variant
Dim col2 As Variant
Dim col3 As Variant
Dim col4 As Variant
Dim col5 As Variant
Dim col6 As Variant
Dim cell As Range
Dim sourceRow As Range: For Each sourceRow In Selection.Rows
col1 = sourceRow.Cells(1).Value
col2 = sourceRow.Cells(2).Value
col3 = sourceRow.Cells(3).Value
col4 = sourceRow.Cells(4).Value
col5 = sourceRow.Cells(5).Value
col6 = sourceRow.Cells(6).Value
For Each cell In sourceRow.Cells
Selection.Worksheet.Cells(targetRowNumber, 1) = col1
Selection.Worksheet.Cells(targetRowNumber, 2) = col2
Selection.Worksheet.Cells(targetRowNumber, 3) = col3
Selection.Worksheet.Cells(targetRowNumber, 4) = col4
Selection.Worksheet.Cells(targetRowNumber, 5) = col5
If Not cell.Column = Selection.Column Then
Selection.Worksheet.Cells(targetRowNumber, 6) = cell.Value
End If
Next cell
targetRowNumber = targetRowNumber + 1
Next sourceRow
End Sub