Hello. I'm trying to create a macro that can change multiple columns into multiple rows. I found the following topic which was close, but I wasn't able to tweak the macro to get it to do what I wanted. http://excel.bigresource.com/Track/excel-OzKIXK1u/
I've attached the file with an example of what I'm trying to do.
Basically, Columns A-E would need to be duplicated 12 times. The headers from Columns F-I would need to be the values in Column F for those rows, and the values from Columns F-I should be the values for Column G and the values from columns J-M should be the values for Column H.
The excel file demonstrates it a little better.
This is the macro I've been trying to tweak, unsuccessfully:
Public Function ReArrange()
Dim LastRow As Long
Dim LastCol As Long
Dim i As Long, j As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1
LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
For j = LastCol - 2 To 2 Step -3
If Application.CountBlank(.Cells(i, j).Resize(, 3)) < 3 Then
.Rows(i + 1).Insert
.Cells(i + 1, "A").Value = .Cells(i, "A").Value
.Cells(i, j).Resize(, 3).Copy .Cells(i + 1, "B")
End If
Next j
If Application.CountBlank(.Cells(i, 2).Resize(, 3)) = 3 Then .Rows(i).Delete
Next i
.Columns("E:P").Delete
End With
End Function
Thanks,
Tester36
Bookmarks