I am trying to move data from rows into columns which may be described with table better -
Name Project 4/10 4/11 4/12
Joe ProjX 8 10 5
Peter ProjY 5 8
In the above table, 1st row is the header row and in the output last 3 column names would become the row values in a column called Date and the integer values would come under a column called Hours. In other words the output should look like the following:
Name Project Date Hours
Joe ProjX 4/10 8
Joe ProjX 4/11 10
Joe ProjX 4/12 5
Peter ProjY 4/10 5
Peter ProjY 4/11 8
Solution
Try this macro
Sub FixRow()
Dim lMaxRows As Long ' max rows of data
Dim lRowBeanCounter As Long ' a row counter
Dim iColCount As Integer 'to see how many columns are used on the row
Dim iColStart As Integer 'the hour starts from this column number
Dim iHourData As Integer ' number of hour data
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
lRowBeanCounter = 1
iColStart = 3
For lRowBeanCounter = lMaxRows To 2 Step -1
iColCount = Cells(lRowBeanCounter, Columns.Count).End(xlToLeft).Column
iHourData = iColCount - iColStart
If iHourData >= 0 Then
If (iHourData > 0) Then
Rows(lRowBeanCounter + 1 & ":" & lRowBeanCounter + iHourData).Insert
End If
Range(Cells(lRowBeanCounter, 1), Cells(lRowBeanCounter, iColStart - 1)).Copy
Range(Cells(lRowBeanCounter, 1), Cells(lRowBeanCounter + iHourData, iColStart - 1)).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False
Range(Cells(lRowBeanCounter, iColStart), Cells(lRowBeanCounter, iColCount)).Copy
Cells(lRowBeanCounter, iColCount + 2).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Range(Cells(1, iColStart), Cells(1, iColCount)).Copy
Cells(lRowBeanCounter, iColCount + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Range(Cells(lRowBeanCounter, iColStart), Cells(lRowBeanCounter + iHourData, iColCount)).Delete Shift:=xlToLeft
End If
Next lRowBeanCounter
iColCount = Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(1, iColStart), Cells(1, iColCount)).Clear
Range("C1") = "Date "
Range("D1") = "Hours"
End Sub
This document, titled « Excel - Moving row values into column values », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).
