Convert long column to rows
In this example, all the data is in column A in Sheet1. The following macro
will copy it into rows on Sheet2:
Sub xformit()
Dim s1 As Worksheet, s2 As Worksheet
Dim i As Long, j As Long, k As Long
Dim n As Long
Set s2 = Sheets("Sheet2")
Set s1 = Sheets("Sheet1")
s1.Activate
j = 1
k = 1
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To n
s2.Cells(j, k).Value = Cells(i, 1).Value
k = k + 1
If k Columns.Count Then
k = 1
j = j + 1
End If
Next
End Sub
--
Gary''s Student - gsnu200839
"Excelchallenged" wrote:
I have exported a large database that is all in one column, how do I change
it to rows? The Copy, Paste Special, Transpose doesn't work because of the
length of the column.
Thank you!
|