Data from multiple variable length columns to one column

[Robert]

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

 

[Dave Peterson]

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.

 

[Guest]

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.

 

[Nick Hodge]

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
(e-mail address removed)

 

[Robert]

Many thanks for the speedy responses

Robert