Need help to create a macro

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:48 PM GMT

Question

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.

Answer

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

©2020 eLuminary LLC. All rights reserved.