CODEWITHSUNDEEP
excelcopy-pastevba
user983988
user983988

Reputation:

Excel - copying text from one cell to another without deleting original content

Basically I have the following scenareo:

2 columns, with 600 rows of data.

I need to copy the data from column 2 and place it at the end of the content in column1 for the same rows. This would result in column 1 having its original content plus the additional content of column 2.

Any information in how I can do this will be greatly appreciated.

Thanks in advance!

Upvotes: 1

Views: 17028

Answers (4)

user983988
user983988

Reputation:

As this question received a number of views (10,000+) I thought it was important to also share another and far simpler solution:

In cell C1 use the formula:

=(A1 & B1)

This will copy the content of cell A1 and B1 into cell C1. Drag the formula to all other rows (row 600 in my case).

Then copy the column and paste using 'values only'.

You will then have cells in column C containing the content of column A and column B in a single cell on a row-to-row basis.

Upvotes: 0

Santosh
Santosh

Reputation: 12353

Just keep it simple. Here is the code.

Sub copyCol()
  Dim lastRow As Long
  lastRow = Range("A65000").End(xlUp).Row
  Range("B1:B" & lasrow).Copy Range("A" & lastRow).Offset(1, 0)
End Sub

Upvotes: 0

David Zemens
David Zemens

Reputation: 53623

This should take the values from column 2 and place them sequentially at the bottom of column 1.

Sub test()
Dim rng1 As Range
Dim rng2 As Range
Dim cl As Range
Dim r As Long

Set rng1 = Range("A1", Range("A1048576").End(xlUp))
Set rng2 = Range("B1", Range("B1048576").End(xlUp))

r = rng1.Rows.Count + 1

For Each cl In rng2
    Cells(r, 1).Value = cl.Value
    r = r + 1
Next

End Sub

Upvotes: 0

David
David

Reputation: 1232

Here's a VBA in a simple form. Just create a macro, add these lines to it. Then select your original column (what you're calling column 1), and run the macro.

a = ActiveCell.Value
b = ActiveCell(1, 2).Value

ActiveCell.Value = a + b

The bracketed cell reference is a relative statement - 1, 2 means "same row, one column to the right" so you can change that if you need. You could make it loop by expanding thusly:

Do
a = ActiveCell.Value
b = ActiveCell(1, 2).Value

ActiveCell.Value = a + b

ActiveCell.Offset(1, 0).Select

If ActiveCell.Value = "" Then
    Exit Do
End If
Loop

That loop will carry on until it finds a blank cell, then it'll stop. So make sure you have a blank cell where you want to stop. You could also add extra characters into the line that combines.. so in the above example it's ActiveCell.Value = a + b, but you could make it ActiveCell.Value = a + " - " + b or anything else that may help.

Upvotes: 1

Related Questions