Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to switch rows | Excel Discussion (Misc queries) | |||
From clumns to rows | Excel Programming | |||
Can You Switch Rows | Excel Discussion (Misc queries) | |||
Can You Switch Rows | Excel Discussion (Misc queries) | |||
how do i transpose rows to clumns? | Excel Worksheet Functions |