Transpose Data in Columns into rows?

Microsoft Excel 2003 (full product)
December 6, 2010 at 18:47:15
Specs: Windows XP
Name Street Address City/State

John Smith
1292 Cedar Road
Greenwood, MD

Thad McDonald
345 Sailview Dr.
Asheville, NC

into:

Name Street Address City/State

John Smith 1292 Cedar Road Greenwood, MD

Thad McDonald 345 Sailview Dr. Asheville, NC

*using macros please


See More: Transpose Data in Columns into rows?

Report •


#1
December 6, 2010 at 19:17:55
Is there a blank line between each address group?

Is each address group always (and only) 3 lines?

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
December 6, 2010 at 19:20:49
Yes for both questions.

Report •

#3
December 6, 2010 at 19:51:15
Assuming your data starts in A1, this code will split the groups into B1, C1, D1 and so on.

I suggest that you try the code in a backup copy of your workbook since macros cannot be undone.

Option Explicit
Sub TransAddress()
Dim numGroups, nxtGroup As Integer
Dim srcRw, lstSrcRw, dstRw, dstCol As Integer
'Find last row with data in Column A
  lstSrcRw = Range("A" & Rows.Count).End(xlUp).Row
'Calculate how many groups in Column A
  numGroups = (lstSrcRw + 1) / 4
'Loop through groups
   For nxtGroup = 1 To numGroups
'Increment destination row
    dstRw = dstRw + 1
'Loop through Destination Columns
     For dstCol = 2 To 5
'Increment Source Row
       srcRw = srcRw + 1
'Set Destination cell equal to Source cell
       Cells(dstRw, dstCol) = Cells(srcRw, 1)
     Next
   Next
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •
Related Solutions


Ask Question