Macro/Repetitive steps in excel

Copper Contributor

Is there a way create a macro to do repetitive steps for moving data from column A rows 1-10 to row 1 columns A-J then move down line 11 and do the same thing for rows 11-20 into columns A-J row 11 without having to edit the Macro?

1 Reply

 

Sub TransposeData()
    Dim r As Long
    Dim m As Long
    Dim c As Long
    Application.ScreenUpdating = False
    m = Cells(Rows.Count, 1).End(xlUp).Row
    For r = 4 To m Step 10
        For c = 4 To 11
            If Cells(r + c - 4, 3).Value <> "" Then
                Cells(r - 2, c).Value = Cells(r + c - 4, 3).Value
            End If
        Next c
    Next r
    Application.ScreenUpdating = True
End Sub

@darylpaul