Concatenate AND Cut ??

[ndh]

Hey all. Thanks in advance for the help!

I am trying to go through a busy spreadsheet and concatenate some cell
together. Easy enough. Except, on each row, the specific cells tha
need to be joined will change. OK, so I made a couple custom function
and can call them by name on each row. But I also want to delete th
old cells after the data has been concatenated. So far I have bee
copying and pasting the values of the concatenated cells, and deletin
the old cells.

There MUST be a way to simplify this process so that the desired cell
are joined together, and then the concatenated text is saved as suc
(not as a function), and then the old cells are deleted.

Any ideas? It would make life a LOt easier!

Thanks,
Nic

 

[Frank Kabel]

Hi
only possible with a macro. something like

sub foo()
dim i
for i=1 to 100
cells(i,1).value=cells(i,1).value & cells(i,2).value
cells(i,2).clearcontents
next i
end sub

 

[JE McGimpsey]

one way:

Attach this macro to a toolbar button or a keyboard shortcut.

Public Sub ConcatAndClear()
Dim vTxtArr As Variant
Dim nTop As Long
Dim i As Long
Dim j As Long
With Intersect(Selection, Selection.Parent.UsedRange)
vTxtArr = .Value
nTop = UBound(vTxtArr, 1)
For i = 1 To nTop
For j = 2 To UBound(vTxtArr, 2)
vTxtArr(i, 1) = vTxtArr(i, 1) & vTxtArr(i, j)
Next j
Next i
ReDim Preserve vTxtArr(1 To nTop, 1 To 1)
.Resize(, 1).Value = vTxtArr
.Offset(0, 1).Resize(, .Columns.Count - 1).ClearContents
End With
End Sub