måndag, oktober 22, 2012

Loop transpose in excel

In order to transpose every five rows in a column into a single row you need to loop the transpose function. This can be timeconsuming but a solution to this can be the following macro:

Option Explicit
 
Public Sub TransposeX5()
 
  Const DoColumn As Integer = 1 ' transpose column A into columns B-F
  Const StartRow As Integer = 1 ' start at row 1
 
  Dim iLastRow As Long
  Dim iRow As Long
  Dim iOffset As Integer
 
  iLastRow = Cells(Rows.Count, DoColumn).End(xlUp).Row
 
  For iRow = StartRow To iLastRow Step 5
    For iOffset = 1 To 5
      Cells(iRow, DoColumn + iOffset) = Cells(iRow + iOffset - 1, DoColumn)
    Next iOffset
  Next iRow
 
End Sub

0 kommentarer: