Transpose columns into Row

Microsoft Microsoft excel 2007 (pc)
February 17, 2011 at 13:58:59
Specs: Windows XP
Addresses that have 4 and five lines need macro that makes a Row for each address. There is a space between each address. I have seen macros that do this for same number of address lines but not for different number of lines.
Thanks,
Jim

See More: Transpose columns into Row

Report •


#1
February 17, 2011 at 18:26:43
You could create a copy and paste special ... chosing to transverse on paste.

I'm still learning everyday.


Report •

#2
February 17, 2011 at 19:47:02
Assuming your address list starts in A1 and that there is a blank cell between each address, this should do it for you.

Sub Trans45()
Dim lastRw, dstCol, dstRw, srcRw As Integer
'Determine last Row with data in Column A
  lastRw = Cells(Rows.Count, 1).End(xlUp).Row
'Initialize destination Row & Column
  dstRw = 1
  dstCol = 1
'Loop through data in Column A
 For srcRw = 1 To lastRw
'If the cell is not empty, then Increment destination _
'column and copy data, starting in Column B, Row 1
   If Cells(srcRw, 1) <> "" Then
     dstCol = dstCol + 1
     Cells(dstRw, dstCol) = Cells(srcRw, 1)
'If cell is empty, increment Row number and
'reset Column Number to 1
   Else:
     dstRw = dstRw + 1
     dstCol = 1
   End If
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
February 18, 2011 at 06:45:36
Worked for smaller sections - got a run time error if I tried the whole file. Cut it in smaller sheets - worked like a charm, thanks!!!
Jim

Report •

Related Solutions

#4
February 18, 2011 at 07:34:26
Thanks macro worked great! Do you program in other languages as well? Do you do programming on the side?

Report •

#5
February 18, 2011 at 07:36:31
Without knowing how your file is set up, I obviously can't comment on the error, but in any case, I'm glad you got it working.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question