Changing Columns to Rows Question

J

James

Is there a way to convert data that is in colums down
x
x
x

To rows x x x

Then if that can be done, Is there a way to store data in a text file
so that it is seperated by a comma like 1,2,3,4,?

Actually what I need to do is find a way to save a column of data in
a file that would load up in note book in a rod seperated by comma's.

Thanks

James
 
A

Andy B

James

Copy your range and use Edit / Paste Special / Transpose. This transposes
rows to columns and columns to rows. If you save this as a .csv file, by
altering the 'Save as file type' option, you should get what you want.

Andy.
 
U

unknowndevice

I am looking for a macro that will transpose a long column of dat
(records seperated by a blank cell in the column) into seperate row
(each row representing a speperate record)
 
D

Dave Peterson

Those blank cells are really empty??? They're not just formulas that evaluate
to ""? And are those cells in column A all values (constants) or all formulas
or a mixture of the two?

If those blank cells are really empty and all the cells are constants, then this
should work:

Option Explicit
Sub testme()
Dim BigRange As Range
Dim SmallArea As Range

With ActiveSheet
Set BigRange = Nothing
On Error Resume Next
Set BigRange = .Range("a:a").Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If BigRange Is Nothing Then
MsgBox "no constants in this column!"
Exit Sub
End If

For Each SmallArea In BigRange.Areas
SmallArea.Copy
SmallArea.Cells(1).Offset(0, 1).PasteSpecial Transpose:=True
Next SmallArea

On Error Resume Next
.Range("b:b").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

.Range("a:a").Delete

End With
End Sub

There's no error checking to see if the number of cells exceeds the number of
columns--any chance that could happen?
 
Top