Archive of Mr Excel Message Board

Back to Edit in Excel archive index
Back to archive home

Changing a row of data to a column of data

Posted by David on February 21, 2001 9:11 PM

I have data that I need to convert from a row to a column so that I can import into a database that requires this format. For example, my data looks like this:
Bob Jones 222 Elm St Anytown, PA
Sally Smith 181 Oak Ct Anytown, PA

I need it to look like this:
Bob
Jones
222 Elm St
Anytown, PA
Sally
Smith
181 Oak Ct
Anytown, PA

Thanks in advance for your help.

Re: Changing a row of data to a column of data

Posted by David Hawley on February 21, 2001 9:33 PM

Bob, select each row and copy the select any single cell on another sheet and go to Edit>PasteSpecial>Transpose.

Dave

OzGrid Business Applications

Re: Changing a row of data to a column of data

Posted by David on February 21, 2001 9:49 PM

David - thanks for the tip, it works great for one row at a time - is there any way to get it to work for all the rows or will this require a macro? Thanks again.

Re: Changing a row of data to a column of data

Posted by David Hawley on February 21, 2001 11:23 PM

Dave, yes it will. Try this one, just change "Sheet2" to the sheet name you are transposing to. Remember also that there are only 256 columns, so only select 256 rows at one time.

Sub TryThis()
Dim i As Long
For i = 1 To Selection.Rows.Count
Selection.Rows(i).Copy
Sheets("Sheet2").Cells(1, i).PasteSpecial Transpose:=True
Next i
End Sub

Dave

OzGrid Business Applications

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.