Combining Cell Contents

[Tom]

Hello,

Need help in moving column contents without overwriting existing
contents. Example - Cell A1 contents is "1234/", cell B2 is "55". Want
to move all of column B to column A without overwriting the cells in
column A. In example cell A1 would end up reading "1234/55".

Thanks,
Tom

 

[Alan]

Hi Tom,

Manually:

Select Column B
Insert Column
Select B1
Insert formula =A1&C1
Copy formula to last Row
Copy Column B
PasteSpecial Values
Select Column A
Delete

With VBA: (probably isn't the best way but works for me)

Sub CombineAandB()
Application.ScreenUpdating = False
Columns("B:B").Insert Shift:=xlToRight
Range("B1").FormulaR1C1 = "=A1&C1"
Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row).FillDown
Columns("B:B").Select
Selection.Copy
Selection.PasteSpecial xlvalues
Columns("A:A").Select
Selection.Delete
Range("A1").Select
Application.ScreenUpdating = True
End Sub

Both do means do replace the contents in column A.

Alan

 

[Gary Keramidas]

one way

Sub Combine_And_B()
Dim ws As Worksheet
Dim lastrow As Long, i As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False

For i = 1 To lastrow ' change 1 to your first row with data
ws.Range("A" & i).Value = ws.Range("A" & i).Value & _
ws.Range("B" & i).Value
Next
Application.ScreenUpdating = True


End Sub

 

[Tom Ogilvy]

Sub ConCatB()
Range("A1").Value = Range("A1").Value & Range("B1").Value
End Sub

 

[Gord Dibben]

Tom

=A1&B1

Or do you want a VBA method.

Sub test()
Dim rng As Range
Set rng = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
For Each cell In rng
cell.Value = cell.Value & cell.Offset(0, 1).Value
' cell.Offset(0, 1).ClearContents 'optional
Next
End Sub


Gord Dibben MS Excel MVP