Help converting column data to rows

[jimbobimbo]

I have some data imported from a text file in a single column that I would like to convert to rows. The "Transpose" function works but I'm having difficulty putting it in a macro that will do this for me.

The data is as follows - 10 cells then a space:

# 1
1 CREATE POINT 213CP01_1:210IQI1560.OUT
1 PUT POINT 213CP01_1:210IQI1560.OUT TYPE FLOAT
DESC = ""210FE102 APRN FDR MTR"""
AUDITUPD = NO
COLLECTOR = hs4401
COLMETH = CONNECTED
ENGUNITS = %
EXPORT = NO
FASTFREQ = 5"

# 2
1 CREATE POINT 215CP01_1:210IQI1631.OUT
1 PUT POINT 215CP01_1:210IQI1631.OUT TYPE FLOAT
DESC = ""210CV102 DRIVE MTR TOTAL""
AUDITUPD = NO
COLLECTOR = hs4401
COLMETH = CONNECTED
ENGUNITS = %
EXPORT = NO
FASTFREQ = 5

Thanks in advance

 

[anotherhiggins]

Try this:

Sub Transpose()
Range(Range("A1"), Range("A1").Offset(ActiveSheet.UsedRange.Rows.Count, 0)).Copy
Range("C1").PasteSpecial Transpose:=True
End Sub

 

[Tom Urtis]

One way:

Sub Test1()
With Application
.ScreenUpdating = False
Dim i As Long, LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).row
i = 1
Do
Range(Cells(i, 2), Cells(i, 12)).Value = .Transpose(Range(Cells(i, 1), Cells(i + 10, 1)))
i = i + 11
Loop While i <= LR
Columns(1).Delete
Columns(1).SpecialCells(4).EntireRow.Delete
Range("A1").CurrentRegion.Columns.AutoFit
.Goto Range("A1"), True
.ScreenUpdating = True
End With
End Sub

 

[jimbobimbo]

Tom,
That works great - thanks a million.