Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i paste rows/columns avoiding hidden rows/columns | Excel Discussion (Misc queries) | |||
Excel 2003 - change columns to rows and rows to columns | Excel Discussion (Misc queries) | |||
Excel 2003 - change columns to rows and rows to columns | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
how to interchange rows to columns & columns to rows in a table | Excel Discussion (Misc queries) |