|
Transposing
I have several hundred rows with names/addresses as follows (each line is a
row): Mr. Smith 123 Sunshine Lane Graver, TX Ms. Johnson 334 Forest Brook Lane P.O. Box 730 Oklahoma City, OK How can I transpose the name/address rows into columns all at once? I'm having a problem since some names/addresses are 3 rows, some are 4, and some are 5 or 6. There is one blank row in between each name/address. Please help because right now I'm doing these one name/address at a time! |
Transposing
You need a macro
Sub transpose() LastRow = Range("A" & Rows.Count).End(xlUp).Row RowCount = 1 ColCount = 2 For LoopCount = 1 To LastRow If Range("A" & (RowCount + 1)) = "" Then ColCount = 2 RowCount = RowCount + 1 Rows(RowCount).Delete Else Cells(RowCount, ColCount) = Range("A" & (RowCount + 1)) ColCount = ColCount + 1 Rows(RowCount + 1).Delete End If Next LoopCount End Sub "Nanapat" wrote: I have several hundred rows with names/addresses as follows (each line is a row): Mr. Smith 123 Sunshine Lane Graver, TX Ms. Johnson 334 Forest Brook Lane P.O. Box 730 Oklahoma City, OK How can I transpose the name/address rows into columns all at once? I'm having a problem since some names/addresses are 3 rows, some are 4, and some are 5 or 6. There is one blank row in between each name/address. Please help because right now I'm doing these one name/address at a time! |
Transposing
Joel, thank you! It worked like a charm!
"Joel" wrote: You need a macro Sub transpose() LastRow = Range("A" & Rows.Count).End(xlUp).Row RowCount = 1 ColCount = 2 For LoopCount = 1 To LastRow If Range("A" & (RowCount + 1)) = "" Then ColCount = 2 RowCount = RowCount + 1 Rows(RowCount).Delete Else Cells(RowCount, ColCount) = Range("A" & (RowCount + 1)) ColCount = ColCount + 1 Rows(RowCount + 1).Delete End If Next LoopCount End Sub "Nanapat" wrote: I have several hundred rows with names/addresses as follows (each line is a row): Mr. Smith 123 Sunshine Lane Graver, TX Ms. Johnson 334 Forest Brook Lane P.O. Box 730 Oklahoma City, OK How can I transpose the name/address rows into columns all at once? I'm having a problem since some names/addresses are 3 rows, some are 4, and some are 5 or 6. There is one blank row in between each name/address. Please help because right now I'm doing these one name/address at a time! |
| All times are GMT +1. The time now is 12:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com