Help Transposing Data from Row To Column

[NetWave128]

I have a single column with data in it. The cells 1-4 contain Name
Address,City, Phone in that order continuous until A240. I would lik
to put the Name, Address,City, Phone accross in individual columns. I
a new sheet. I would like to use a vba macro without a loop if at al
possible.

Thank You for your help

Attachment filename: screenshot1.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=43452

 

[Alan Beban]

Why do you want to avoid a loop?

Alan Beban

 

[NetWave128]

I would prefer no loop because I don't know how to edit it...however if
that is the only way to do it, I probably should.

Additionally, the original data has a blank row(cell) between each set
of 4 properties. Is there anyway to to a copy.pastespecial.transpose
Skip blanks and have it correctly put the trasnposed data in multiple
rows instead of a sinlgle row as it is doing now.

thanks again

 

[Tom Ogilvy]

No, there is no magic function that recognizes you want to transpose groups
of 4/5 rows into the format you want.

You can write formulas on the new sheet then drag fill these down and they
will do what you want.

You can then copy them and do a pastespecial values to replace the formulas
with the values you retrieved.

Post back if interested in that approach.

 

[Dave Peterson]

If you were doing it manually (yech!), you'd do each area separately.

You can have a macro do the same thing.

Option Explicit
Sub testme()

Dim myRange As Range
Dim myArea As Range
Dim curWks As Worksheet
Dim newWks As Worksheet
Dim oRow As Long

Set curWks = ActiveSheet
Set newWks = Worksheets.Add

With curWks
Set myRange = Nothing
On Error Resume Next
Set myRange = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) _
.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If myRange Is Nothing Then
MsgBox "No constants found in column A!"
Exit Sub
End If

oRow = 1
For Each myArea In myRange.Areas
myArea.Copy
newWks.Cells(oRow, "A").PasteSpecial Transpose:=True
oRow = oRow + 1
Next myArea
End With

End Sub

This looks for constants in column A--no formulas. But it could be modified.

 

[Alan Beban]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you can
array enter into a five column range with enough rows to accommodate all
the addresses. E.g., assuming 250 addresses in A1:A1000, array enter
into b1:f250

=ArrayReshape(A1:A1000,250,5)

Alan Beban

 

[Alan Beban]

Actually, because of the blank lines, it would be 200 addresses, so
=ArrayReshape(A1:A1000,200,5)

Alan Beban

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you can
array enter into a five column range with enough rows to accommodate all
the addresses. E.g., assuming 250 addresses in A1:A1000, array enter
into b1:f250

=ArrayReshape(A1:A1000,250,5)

Alan Beban