Search code examples
excelvbacopy-paste

VBA : Copy values from 2 or more columns and paste them in 1 column without overwriting the values

I'm new to VBA and this is my first post here, so please excuse my amateurish question, but how do I copy values from 2 or more columns and paste them in 1 column without overwriting the values (i.e. values pasted in a single column successively).....the columns containing the values are U,V and W to be pasted in column AB.....

Please find the code below:

Private Sub CommandButton1_Click()

Dim a As Integer
Range("u1").Select
Noofcolumns = Range(Selection, Selection.End(xlToRight)).Columns.Count

For i = 1 To Noofcolumns
    Cells(1, 20 + i).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    For j = 1 To 500
        a = Cells(j, 28).Value
        If IsEmpty(a) Then
            Cells(j, 28).Select
            Selection.PasteSpecial Paste:=xlPasteValues
        Else: GoTo 1
        End If
    Next j

Next i

End Sub
Solution

  • Some things for you to consider:

    • Have a read on how to avoid the use of .Select. One of the most shared posts on SO here I think, and a great guide in better referencing Range object.

    • To build upon the first point, you would want to be explicit referencing Range objects. For example Range("u1").Select will select U1 on the currently active worksheet. Instead, at least, use a worksheet reference (even a workbook reference could be better)

    • Secondly, you have used XlToRight to retrieve the last used column. If this was your intention it might be just fine. But for future reference, if there is a gap in your data, you might end up with a Range you not happy with. XlToLeft might be better, for example the below would find the last used column in the first row from the right to the left:

      With Sheet1
    LastColumn = .Cells(1, sht.Columns.Count).End(xlToLeft).Column
End with

      In your case such assesment might not even be needed since your values are in columns U:W, instead your can just use a For x = # to # loop.

    • The same counts for when you want to find a last used row. A blank cell can throw off XlDown, but XlUp will counter that. A more in depth post on finding a last row can be found here, also a highly valuable SO post. For example, the below will get the last used row from column A:

      With Sheet1
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End with

    • Another thing is that you won't need to use Copy to transfer values, you can do so directly, for example (simplified):

      With Sheet1
    .Cells(1, 1) = .Cells(1, 2)
End with

    • Another (minor) point is that there is no use in using Integer data type variables. They overflow easily if misused causing errors. You better of using Long data types.

    • Try and avoid Goto statemtents, this cause spaghetti code and in your case there isn't even a statement missing (goto has nowhere to actually go)

    Now with those points you can try to alter your code, which now could look like the below:

    Sample Data:

    enter image description here

    Sample Code:

    Private Sub CommandButton1_Click()

'Dimming our variables properly
Dim lr1 As Long, lr2 As Long, x As Long

'Using an explicit sheet reference
With Sheet1

    'Looping over the columns U:W
    For x = 21 To 23

        'Getting the last used row from the column
        lr1 = .Cells(.Rows.Count, x).End(xlUp).Row

        'Getting the last used row from column J
        lr2 = .Cells(.Rows.Count, 28).End(xlUp).Row + 1

        'Transfer data directly
        .Cells(lr2, 28).Resize(lr1 - 1).Value2 = .Range(.Cells(2, x), .Cells(lr1, x)).Value2

    'Continue to next column in iteration
    Next x

End With

End Sub

    Result:

    enter image description here

    All the above was in the assumption you have a header in all these column. If not, simply adjust accordingly.