Forum Discussion
Solved
Need Help with turning columns into rows
I have a table of workers and for each a set of columns that indicate information that needs to be moved to a linear format. I changed the workers to fruits to make it easier to understand, but t...
Sub worker() Dim i As Long Dim j As Long Dim m As Long Dim k As Long Dim l As Long Dim n As Long Range(Cells(15, 1), Cells(1048576, 5)).Clear i = Range("A" & Rows.Count).End(xlUp).Row m = 15 For j = 2 To i Range(Cells(m, 1), Cells(m + 2, 1)).Value = Cells(j, 1).Value Range(Cells(m, 2), Cells(m, 5)).Value = Range(Cells(j, 2), Cells(j, 5)).Value Range(Cells(m + 1, 2), Cells(m + 1, 5)).Value = Range(Cells(j, 6), Cells(j, 9)).Value Range(Cells(m + 2, 2), Cells(m + 2, 5)).Value = Range(Cells(j, 10), Cells(j, 13)).Value m = m + 3 Next j l = Range("A" & Rows.Count).End(xlUp).Row For n = 15 To l If IsEmpty(Cells(n, 2)) And Not (IsEmpty(Cells(n, 1))) Then Range(Cells(n, 1), Cells(n, 5)).Delete n = n - 1 Else End If Next n End SubMaybe with these lines of code. In the attached file you can click the button in cell F10 to run the macro. If the table with original data is large (e.g. 1000 rows) the result table can be in columns P to T for example.
Sub worker()
Dim i As Long
Dim j As Long
Dim m As Long
Dim k As Long
Dim l As Long
Dim n As Long
Range(Cells(15, 1), Cells(1048576, 5)).Clear
i = Range("A" & Rows.Count).End(xlUp).Row
m = 15
For j = 2 To i
Range(Cells(m, 1), Cells(m + 2, 1)).Value = Cells(j, 1).Value
Range(Cells(m, 2), Cells(m, 5)).Value = Range(Cells(j, 2), Cells(j, 5)).Value
Range(Cells(m + 1, 2), Cells(m + 1, 5)).Value = Range(Cells(j, 6), Cells(j, 9)).Value
Range(Cells(m + 2, 2), Cells(m + 2, 5)).Value = Range(Cells(j, 10), Cells(j, 13)).Value
m = m + 3
Next j
l = Range("A" & Rows.Count).End(xlUp).Row
For n = 15 To l
If IsEmpty(Cells(n, 2)) And Not (IsEmpty(Cells(n, 1))) Then
Range(Cells(n, 1), Cells(n, 5)).Delete
n = n - 1
Else
End If
Next n
End SubMaybe with these lines of code. In the attached file you can click the button in cell F10 to run the macro. If the table with original data is large (e.g. 1000 rows) the result table can be in columns P to T for example.
This works great but I thought I could build onto the code (my data set is much larger than this example).....i've been trying the past few days and can't seem to figure it out. Can someone please help me with two things....one, i need the results of the macro to go to a different worksheet. Also, i need to add columns to capture future changes. (this has change 1, change 2, i'll need to go up to change 6...,.and i thought i could easily just add to this, but i'm stumped!!
Tami_E Perhaps you should forget about VBA and go for PowerQuery instead.
