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.

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

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.

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