Looping through clumns and switch to rows
 

I need to convert

Customer B 1457895 1248875 45345345 45345434
Customer X 6548464 6549846 68462184 6548414 6354654


to

Customer B 1457895
Customer B 1248875
Customer B 45345345
Customer B 45345434
Customer X 6548464
Customer X 6549846
Customer X 68462184
Customer X 6548414
Customer X 6354654

It is several lines ad as many as 225 columns per line.

Any help is appreciated.

Looping through clumns and switch to rows
 

Is there a column between Customer X and the first number? Are there any
empty columns between the numbers?

"mjmcevoy" wrote:

I need to convert

Customer B 1457895 1248875 45345345 45345434
Customer X 6548464 6549846 68462184 6548414 6354654


to

Customer B 1457895
Customer B 1248875
Customer B 45345345
Customer B 45345434
Customer X 6548464
Customer X 6549846
Customer X 68462184
Customer X 6548414
Customer X 6354654

It is several lines ad as many as 225 columns per line.

Any help is appreciated.

Looping through clumns and switch to rows
 

Nope. First column contains the customer names. over 100 customers.

Then the rest of the columes contain account numbers that pretain to each
customer. No blank columns in between

"StumpedAgain" wrote:

Is there a column between Customer X and the first number? Are there any
empty columns between the numbers?

"mjmcevoy" wrote:

I need to convert

Customer B 1457895 1248875 45345345 45345434
Customer X 6548464 6549846 68462184 6548414 6354654


to

Customer B 1457895
Customer B 1248875
Customer B 45345345
Customer B 45345434
Customer X 6548464
Customer X 6549846
Customer X 68462184
Customer X 6548414
Customer X 6354654

It is several lines ad as many as 225 columns per line.

Any help is appreciated.

Looping through clumns and switch to rows
 

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim oRow As Long
Dim HowMany As Long

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 1
For iRow = FirstRow To LastRow
'subtract column A
HowMany = .Cells(iRow, .Columns.Count).End(xlToLeft).Column - 1

NewWks.Cells(oRow, "A").Resize(HowMany, 1).Value _
= .Cells(iRow, "A").Value

.Range(.Cells(iRow, "B"), _
.Cells(iRow, .Columns.Count).End(xlToLeft)).Copy
NewWks.Cells(oRow, "B").PasteSpecial Transpose:=True

oRow = oRow + HowMany
Next iRow
End With

End Sub


mjmcevoy wrote:

I need to convert

Customer B 1457895 1248875 45345345 45345434
Customer X 6548464 6549846 68462184 6548414 6354654

to

Customer B 1457895
Customer B 1248875
Customer B 45345345
Customer B 45345434
Customer X 6548464
Customer X 6549846
Customer X 68462184
Customer X 6548414
Customer X 6354654

It is several lines ad as many as 225 columns per line.

Any help is appreciated.

--

Dave Peterson

Looping through clumns and switch to rows
 

Here's my solution. It's a littel messy, but it works!

(Mind any text wrapping)

Sub Reorganize()

Dim glcount, numrows, i, j, l, k As Integer
Dim currentrow As Integer

With ActiveSheet.Range("A1") 'or wherever you start
numrows = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
End With

Range("A1").Select

For l = 1 To numrows

With ActiveSheet.Range("A1").Offset(k, 0) 'or wherever you start
MsgBox (ActiveSheet.Range("A1").Offset(k, 0).Address)
glcount = Range(.Offset(0, 1), .End(xlToRight)).Columns.Count
End With

j = 0

For i = 1 To glcount - 1
currentrow = Range("A1").Offset(i + j + k - 1, 0).Row
Rows(currentrow).Offset(1, 0).Select
Selection.Insert Shift:=xlDown
Cells(j + 1 + k, i + 2).Cut Destination:=Range("A1").Offset(i + k, 1)
Cells(j + 1 + k, "A").Copy Destination:=Range("A1").Offset(i + k, 0)
Next i

k = k + i

Next l

End Sub

"mjmcevoy" wrote:

Nope. First column contains the customer names. over 100 customers.

Then the rest of the columes contain account numbers that pretain to each
customer. No blank columns in between

"StumpedAgain" wrote:

Is there a column between Customer X and the first number? Are there any
empty columns between the numbers?

"mjmcevoy" wrote:

I need to convert

Customer B 1457895 1248875 45345345 45345434
Customer X 6548464 6549846 68462184 6548414 6354654


to

Customer B 1457895
Customer B 1248875
Customer B 45345345
Customer B 45345434
Customer X 6548464
Customer X 6549846
Customer X 68462184
Customer X 6548414
Customer X 6354654

It is several lines ad as many as 225 columns per line.

Any help is appreciated.

Looping through clumns and switch to rows
 

Oops! I forgot to take out my message box line!

delete:
MsgBox (ActiveSheet.Range("A1").Offset(k, 0).Address)

It was part of my testing phase. :)

"mjmcevoy" wrote:

Nope. First column contains the customer names. over 100 customers.

Then the rest of the columes contain account numbers that pretain to each
customer. No blank columns in between

"StumpedAgain" wrote:

Is there a column between Customer X and the first number? Are there any
empty columns between the numbers?

"mjmcevoy" wrote:

I need to convert

Customer B 1457895 1248875 45345345 45345434
Customer X 6548464 6549846 68462184 6548414 6354654


to

Customer B 1457895
Customer B 1248875
Customer B 45345345
Customer B 45345434
Customer X 6548464
Customer X 6549846
Customer X 68462184
Customer X 6548414
Customer X 6354654

It is several lines ad as many as 225 columns per line.

Any help is appreciated.