Changing Data Layout Excel VBA

[lees57]

Hi everyone!!
I just signed up today to ask for help.
I really need help on VBA.
My data looks like below

123.jpg



Is there good way to change the data layout using VBA? I am literally new to VBA.
I would be super appreciated with your help!!

[Paul_Hossler]

Welcome to the forum - please take a minute and read the FAQs at the link in my signature


Put this into a standard module, or tie it to button code

Option Explicit


Sub TwoD_to_OneD()
    Dim rSrc As Range, rDest As Range
    Dim x As Long, y As Long, z As Long
    
    Application.ScreenUpdating = False
    
    Set rSrc = ActiveSheet.Range("B3").CurrentRegion '   <<<<<<<<<<<<<<<< change B3
    Set rDest = ActiveSheet.Range("K3")             '   <<<<<<<<<<<<<<<<< change K3
    
    rDest.CurrentRegion.ClearContents
    
    z = 0
    rDest.Offset(z, 0).Value = "X"
    rDest.Offset(z, 1).Value = "Y"
    rDest.Offset(z, 2).Value = "Z"
    
    For x = 2 To rSrc.Rows.Count
        For y = 2 To rSrc.Columns.Count
            z = z + 1
            rDest.Offset(z, 0).Value = rSrc.Cells(x, 1).Value
            rDest.Offset(z, 1).Value = rSrc.Cells(1, y).Value
            rDest.Offset(z, 2).Value = rSrc.Cells(x, y).Value
        Next y
    Next x


    Application.ScreenUpdating = True


End Sub

[lees57]

Thank you!!