Rows to columns
 

Hello,

I have a mailing list that is in rows rather than columns. Ie a complete
address takes up 4 rows in the same column.

Is there any way to columnize the data that I have to do a mail merge?

Chris

Rows to columns
 

Use the macro below;
It assumes your addresses are in Col A in Sheet1... this will write them in
Sheet2

Sub Transpose()
Dim srcSheet As String
Dim destSheet As String
Dim i, j, lastRow, step As Long

srcSheet = "Sheet1"
destSheet = "Sheet2"
step = 4

Worksheets(srcSheet).Activate

With Worksheets(srcSheet)
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

lastRow = (lastRow / step)
j = 1

For i = 1 To lastRow
Worksheets(srcSheet).Range("A" & j & ":A" & (j + step - 1)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(destSheet).Cells(i, 1).PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
j = j + step
Next i
End Sub
----------------------------
Procedure to run the macro
Open Excel and open the workbook with your data
Press ALT-F11 to open VB Editor
Choose Insert-Module
Paste the code
Press F5

Swich back to the workbook and verify the data in Sheet2...

"Chris" wrote:

Hello,

I have a mailing list that is in rows rather than columns. Ie a complete
address takes up 4 rows in the same column.

Is there any way to columnize the data that I have to do a mail merge?

Chris

Rows to columns
 

Thanks. I had to clean up my data a little and got the job done.

"Sheeloo" wrote:

Use the macro below;
It assumes your addresses are in Col A in Sheet1... this will write them in
Sheet2

Sub Transpose()
Dim srcSheet As String
Dim destSheet As String
Dim i, j, lastRow, step As Long

srcSheet = "Sheet1"
destSheet = "Sheet2"
step = 4

Worksheets(srcSheet).Activate

With Worksheets(srcSheet)
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

lastRow = (lastRow / step)
j = 1

For i = 1 To lastRow
Worksheets(srcSheet).Range("A" & j & ":A" & (j + step - 1)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(destSheet).Cells(i, 1).PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
j = j + step
Next i
End Sub
----------------------------
Procedure to run the macro
Open Excel and open the workbook with your data
Press ALT-F11 to open VB Editor
Choose Insert-Module
Paste the code
Press F5

Swich back to the workbook and verify the data in Sheet2...

"Chris" wrote:

Hello,

I have a mailing list that is in rows rather than columns. Ie a complete
address takes up 4 rows in the same column.

Is there any way to columnize the data that I have to do a mail merge?

Chris