Rows And Columns

Closed
Siva - Jun 29, 2015 at 06:47 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Jun 29, 2015 at 11:33 AM
Hello,


I have the ffg problem, i have a table as below:

PN H1 H2 H3 H4
A AB AC AD AE
B AB AD
C AC AE
D AB AC AE


and I want it to look like

A AB
A AC
A AD
A AE
B AB
B AD
C AC
C AE
D AB
D AC
D AE


Please assist in how i can achieve this with a formula if possible.

1 reply

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jun 29, 2015 at 11:33 AM
Hi Siva:


Not sure what a ffg problem is, but try the following code:
Sub RunMe()
Dim lRow, x As Integer
Dim cCol As Integer

lRow = Range("A1").End(xlDown).Row

For x = lRow To 2 Step -1
    cCol = Cells(x, Columns.Count).End(xlToLeft).Column
    Rows(x + 1).Resize(cCol - 2).Insert
    Range(Cells(x, "B"), Cells(x, cCol)).Copy
    Cells(x, "F").PasteSpecial Transpose:=True
Next x
    
Range("B2:E" & Rows.Count).Delete shift:=xlToLeft
    
Application.CutCopyMode = False
End Sub


How to implement and run a code:

- From Excel hit Alt + F11 to open the "Microsoft Visual Basic" window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro's.
- Double-click the macro you wish to run.
NOTE: macro's cannot be reversed using the blue arrows. Always make sure you save your file before running a code, so you can reopen your file if something unforeseen happens or you want to go back to the situation before the code was run.


Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
0