QUESTION: i need help to create a macro that can CONCATINATE text in selected cells (in rows) and bring it to the first cell of the selection. (The selection of cells should not be fixed). For Example, if i select text in cells A2:A10, it should CONCATINATE and bring the text to A2. (I have already found a macro for this in google search)
What i want the macro to do now is to delete the text in Cells A3:A10, since they are no longer required. (I have not been able to find anything that can combine with the above macro)
Any help would be appreciated. ANSWER: Sub concat()
Dim rowOfCells As Range, oneCell As Range Dim iCol As Long Dim strConcat As String Set rowOfCells = Selection If rowOfCells.Cells.Count = 1 Then MsgBox "must select more than 1 cell" Exit Sub ElseIf rowOfCells.Rows.Count > 1 Then MsgBox "must select single row" Exit Sub End If strConcat = rowOfCells.Range("a1").Value For iCol = 2 To rowOfCells.Columns.Count Set oneCell = rowOfCells.Cells(1, iCol) strConcat = strConcat & oneCell.Value oneCell.ClearContents Next iCol rowOfCells.Range("a1").Value = strConcat
End Sub
---------- FOLLOW-UP ----------
QUESTION: Thank you so much for your response. This is exactly what I was looking for. However, there is a slight problem. I wanted this formula to work in rows. Like to concatenate the data in (A2:A10) but currently it is concatenating data in columns (A2: H2). The other thing that I forgot to mention in the original request was, when the data from different cells is concatenated, it should have "space" between them. Thank you so much for working on this for me.
Sub concat()
Dim colOfCells As Range, oneCell As Range Dim iRow As Long Dim strConcat As String Set colOfCells = Selection If colOfCells.Cells.Count = 1 Then MsgBox "must select more than 1 cell" Exit Sub ElseIf colOfCells.Columns.Count > 1 Then MsgBox "must select single column" Exit Sub End If strConcat = colOfCells.Range("a1").Value & " " For iRow = 2 To colOfCells.Rows.Count Set oneCell = colOfCells.Cells(iRow, 1) strConcat = strConcat & oneCell.Value & " " oneCell.ClearContents Next iRow colOfCells.Range("a1").Value = Trim(strConcat)
End Sub
Advertisement