Turning rows of equal number of columns into 1 continuous row


C

chimerical

I've got a worksheet with about 50+ rows of 5-column's worth of numbers
(a
5x50 grid). I'm trying to turn this into 1 continuous row that uses
data
from A1, B1, C1, D1, E1, A2, B2, C2, D2, E2, A3, B3...


Before:

A1 B1 C1 D1 E1
A2 B2 C2 D2 E2
A3 B3 C3 D3 E3

After:
A1 B1 C1 D1 E1 A2 B2 C2 D2 E2 A3 B3 C3 D3 E3


Is there any way to automate some of this? Thanks.
 
Ad

Advertisements

J

jlepack

What do you mean, "some of this" ??? Try this.

Public Sub oneLoveUmmmRow()
Dim wsF As Worksheet, wsT As Worksheet
Dim rF As Range, rT As Range

' initialize
Set wsF = ActiveSheet
Set wsT = Sheets.Add
wsT.Name = "Results"
Set rF = wsF.Range("A1")
Set rT = wsT.Range("A1")

' rf loops through all the values in wf
' rt just moves across the sheet popping values until rf is done
Do While Not rF.Value = ""
Do While Not rF.Value = ""
rT.Value = rF.Value
Set rT = rT.Offset(0, 1)
Set rF = rF.Offset(0, 1) ' move rf one space right
Loop
Set rF = wsF.Range("A" & rF.Row + 1) ' move rf to the start of
the next row
Loop

' clean up
Set rF = Nothing
Set rT = Nothing
Set wsF = Nothing
Set wsT = Nothing
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top