Thread: Combine several columns to one

Yes. What are the ranges and where are they to be combined ?

Sounds like we might be opening a can of VBA worms but in terms of your specific example...

Public Sub Example()
Dim vData As Variant
vData = Range("A11:E45").Value
For lngCol = 1 to UBound(vData,2) Step 1
    Cells(Rows.Count,"F").End(xlUp).Offset(1).Resize(UBound(vData,1)).Value = Application.Index(vData,0,lngCol)
Next lngCol
End Sub

above would paste into F2 onwards (or from first non blank in F onwards)

Hopefully that helps in part.

It depends... given how varied the selection can be one approach would be something like:

Public Sub Example()
Dim rngSource As Range, rngArea As Range, rngOutput As Range, vData As Variant, lngCol As Long
On Error Resume Next
Set rngSource = Selection
Set rngOutput = Application.InputBox("Select Output Point", Type:=8)
On Error GoTo 0
If rngOutput Is Nothing Or rngSource Is Nothing Then
    MsgBox "Invalid Input Range / Output Point - Routine Terminated", vbCritical, "Error"
Else
    For Each rngArea In rngSource.Areas
        If rngArea.Cells.Count > 1 Then
            vData = rngArea.Value
            For lngCol = 1 To UBound(vData, 2) Step 1
                Cells(Rows.Count, rngOutput.Column).End(xlUp).Offset(1).Resize(UBound(vData, 1)).Value = Application.Index(vData, 0, lngCol)
            Next lngCol
        Else
            Cells(Rows.Count, rngOutput.Column).End(xlUp).Offset(1).Value = rngArea.Value
        End If
    Next rngArea
End If
Set rngOutput = Nothing
Set rngSource = Nothing
End Sub

but this is still open to error depending on size of range areas etc... output point being mid way up a blank column etc...

Have you actually tried your own suggestion ?

Why the clipboard ?

regards using the Clipboard with VBA

see: http://www.cpearson.com/excel/Clipboard.aspx

Given the thread is now running in two places (and you have responses on the other) I myself won't spend time large amounts of my time looking at this I'm afraid.


Also as detailed in the infraction you are obliged to provide links to your cross post(s).

Originally Posted by DonkeyOte

Have you actually tried your own suggestion ?

Good observation. It bothers me that I have to put commas or "&" between cell references to concatenate a range.

I can do if a range reference works with Concatenate.