Hi Howard,

Am Sat, 24 May 2014 17:24:19 +0200 schrieb Claus Busch:

the code below is for universal use:

and with error handling if wrong source or target column is chosen:

Sub TransformCol()
Dim Info As String
Dim LRow As Long, i As Long, j As Long
Dim arrOut As Variant
Dim arrInfo As Variant
Dim SourceCol As Long, TargetCol As Long

Start:
Info = Application.InputBox("Enter the number of rows," _
& "the source column and the target column comma separated", _
"Infos", Type:=2)

If Info = "" Or Info = "False" Then Exit Sub

arrInfo = Split(Info, ",")

SourceCol = Columns(UCase(arrInfo(1))).Column
TargetCol = Columns(UCase(arrInfo(2))).Column

With Sheets("Sheet2")
LRow = .Cells(Rows.Count, SourceCol).End(xlUp).Row

If LRow < arrInfo(0) Or LRow = 1 Then
MsgBox "Wrong source column"
GoTo Start
End If

If TargetCol + Int(LRow / arrInfo(0)) + 1 16384 Then
MsgBox "Not enough columns available"
GoTo Start
End If

For i = 1 To LRow Step arrInfo(0)
arrOut = .Range(.Cells(i, SourceCol), _
.Cells(i + arrInfo(0) - 1, SourceCol))
Sheets("Sheet3").Cells(1, TargetCol + j) _
.Resize(rowsize:=arrInfo(0)) = arrOut
j = j + 1
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional