Excel - Moving row values into column values


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 


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


Thanks to [rizvisa1] for this tip on the forum.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
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).