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
|