lees57
05-14-2020, 05:17 AM
Hi everyone!!
I just signed up today to ask for help.
I really need help on VBA.
My data looks like below
26673
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
05-14-2020, 06:45 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.