Concatenate two selected cells and delete

Question

I have data in column A with 1000 rows of data. I need to concatenate any two selected cells. Example, data in user selected cells A10 & A11 gets concatenated in cell A10. After this I need to clear content of cell A11.

The code below does the good job of concatenation, and puts the data in A10. But it does not clear the contents of the A11 cell data. I need to clear the A11 cell contents.

Any help would be much appreciated.

Option Explicit
Sub MergeStems()

  Dim ColFirst As Long
  Dim ColLast As Long
  Dim JoinedValue As String
  Dim RowCrnt As Long
  Dim RowFirst As Long
  Dim RowLast As Long

    RowFirst = Selection.Row 
    RowLast = RowFirst + Selection.Rows.Count - 1

    ColFirst = Selection.Column
    ColLast = ColFirst + Selection.Columns.Count - 1

    If ColFirst <> 1 Or ColLast <> 1 Then
      Call MsgBox("Please select a range within column ""A""", vbOKOnly)
      Exit Sub
    End If

    With Worksheets("Sheet1")      ' Worksheet of your choice.
      JoinedValue = .Cells(RowFirst, "A").Value
      For RowCrnt = RowFirst + 1 To RowLast
        JoinedValue = JoinedValue & " " & .Cells(RowCrnt, "A").Value
      Next
      .Cells(RowFirst, "A").Value = JoinedValue
    End With

  End Sub

Nick Nick · Accepted Answer · 2016-11-10T08:19:03

Add this just before the "end with"

.Cells(RowFirst + 1, 1).Resize(Selection.Rows.Count - 1, 1).ClearContents

NOTE: Dont miss out the dot at the beginning

EDIT: Try this now. tested on my pc and worked

Concatenate two selected cells and delete

1 Answers