Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data from multiple variable length columns to one column
I would like to copy and paste 93 variable length columns into one
continuous column. I understand that I can use the following to locate the first unused cell and paste the copied columns data: Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteValues I imagine that what I need is some form of loop to pick up the data in the 93 columns. Grateful for any advice -- Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data from multiple variable length columns to one column
One way:
Option Explicit Sub testme() Dim curWks As Worksheet Dim newWks As Worksheet Dim iCol As Long Dim DestCell As Range Dim rngToCopy As Range Set curWks = Worksheets("sheet1") Set newWks = Worksheets.Add Set DestCell = newWks.Range("a1") With curWks For iCol = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column Set rngToCopy = .Range(.Cells(1, iCol), _ .Cells(.Rows.Count, iCol).End(xlUp)) DestCell.Resize(rngToCopy.Rows.Count, 1).Value _ = rngToCopy.Value With newWks Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With Next iCol End With End Sub Instead of copy|paste special|values, I just assigned the value. Robert wrote: I would like to copy and paste 93 variable length columns into one continuous column. I understand that I can use the following to locate the first unused cell and paste the copied columns data: Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteValues I imagine that what I need is some form of loop to pick up the data in the 93 columns. Grateful for any advice -- Robert -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data from multiple variable length columns to one column
Robert; I didn't code and test this. But your answer may look like the
following: dim intI as integer dim strConcatenatedString as string dim StartingColumn as integer dim EndingColumn as integer dim intRow as integer dim intAnsRow as integer dim intAnsCol as integer StartingColumn = 1 ' Sets the starting column to column A. EndingColumn = 93 ' Stest the ending column to column CO. Must check how 'many columns you may have. intRow = 2 'Set the row to row 2. intAnsRow = 3 'Don't know where you want to put the answer. intAnsCol = 1 'Don't know where you want to put the answer. strConcatenatedString = "" 'Set string to null string to start. for intI = StartingColumn to EndingColumn strConcatenatedString = strContenatedString & _ activesheet.cells(intRow,intI).value next intI 'Write out the answer. activesheet.cells(intAnsRow, intAnsCol) = strConcatenatedString 'Note you may want to put commas or spaces between each entry in the output 'string. If so then use something like this: strConcatenatedString = strContenatedString & ", " _ activesheet.cells(intRow,intI).value Hope this helps so early on a Sunday morning. "Robert" wrote: I would like to copy and paste 93 variable length columns into one continuous column. I understand that I can use the following to locate the first unused cell and paste the copied columns data: Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteValues I imagine that what I need is some form of loop to pick up the data in the 93 columns. Grateful for any advice -- Robert |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data from multiple variable length columns to one column
Robert
The code below may give you a start Sub PasteColumnsToOne() Dim iColumns As Integer, x As Integer Dim lRows As Long, lLastRow As Long iColumns = Range("IV1").End(xlToLeft).Column For x = 1 To iColumns lRows = Cells(65536, x).End(xlUp).Row lLastRow = Range("A65536").End(xlUp).Row + 1 If x < 1 Then Range(Cells(1, x), Cells(lRows, x)).Copy Range("A" & lLastRow).PasteSpecial Paste:=xlPasteValues End If Next x End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Robert" wrote in message ... I would like to copy and paste 93 variable length columns into one continuous column. I understand that I can use the following to locate the first unused cell and paste the copied columns data: Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteValues I imagine that what I need is some form of loop to pick up the data in the 93 columns. Grateful for any advice -- Robert |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data from multiple variable length columns to one column
Many thanks for the speedy responses
Robert In message , Robert writes I would like to copy and paste 93 variable length columns into one continuous column. I understand that I can use the following to locate the first unused cell and paste the copied columns data: Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteValues I imagine that what I need is some form of loop to pick up the data in the 93 columns. Grateful for any advice |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Length Columns | Excel Discussion (Misc queries) | |||
Graphing Variable-length columns | Charts and Charting in Excel | |||
Referencing a Value in variable length columns. | Excel Worksheet Functions | |||
Sum a Column of Variable length | Excel Discussion (Misc queries) | |||
Sum a column of variable length? | Excel Discussion (Misc queries) |