Concatenate Dynamic range and place on first cell of same selected range

[raghavakl]

Hi,

I would like to know if it possible to concatenate multiple cells in aa column (selction will vary - sometimes 3, sometimes 5) and place the concatenated value with delimiter "space" in the selected range top cell

if A1:A3 is my range Result A1 = A1&" "&A@&" "&A3
if A1:A5 is my range Result A1 = A1&" "&A@&" "&A3&" "&A4&" "&A5

Thanks in advance

 

[Peter_SSs]

You cannot do that with a formula but it could be done with vba. Is that acceptable?
Of course it would also mean the the individual value that was in cell A1 is lost apart from it now appearing as the first item in the result string.

Will it be possible that any cells within the range are empty? If so, how should they be treated in the final concatenation?

 

[raghavakl]

Yes, that's what is required, - VBA

YEs A1 previous value will be lost.

coming to Empty cells - only extra space will be added - if possible exclude that empty cell

Thanks a lot

 

[Peter_SSs]

OK, see if this suits what you want

Sub Concat_Cells()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Cells(1).Value = Application.Trim(Join(Application.Transpose(.Value)))
  End With
End Sub

 

[raghavakl]

OK, see if this suits what you want

Sub Concat_Cells()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Cells(1).Value = Application.Trim(Join(Application.Transpose(.Value)))
  End With
End Sub

yeah it does but

when my range is from A6:A12, i want in A6, this will change for every occurrence,

though the column is A, but the ranges are A1:A5,A6:A10,A11:A13.... i want results in A1,A6,A10 similarly
hope I am clear

 

[Peter_SSs]

though the column is A, but the ranges are A1:A5,A6:A10,A11:A13.... i want results in A1,A6,A10 similarly
hope I am clear

Certainly clearer than before.

Try

Sub Concat_Cells_v2()
  Dim i As Long
  
  For i = 1 To Range("A" & Rows.Count).End(xlUp).Row Step 5
    With Cells(i, "A").Resize(5)
      .Cells(1).Value = Application.Trim(Join(Application.Transpose(.Value)))
    End With
  Next i
End Sub

 

[raghavakl]

Certainly clearer than before.

Try

Sub Concat_Cells_v2()
  Dim i As Long
 
  For i = 1 To Range("A" & Rows.Count).End(xlUp).Row Step 5
    With Cells(i, "A").Resize(5)
      .Cells(1).Value = Application.Trim(Join(Application.Transpose(.Value)))
    End With
  Next i
End Sub

Sorry but this time, its taking steps 5,

please see the attached picture

i will select A4:A7 and run function
then again A8:A9 and run func
then again A10:A15 and run func
then likewise it will change the range size every time - its not standard.

in picture the Column C will be reflected selection wise in Column A itself.

the Range size is not fixed, that is the main issue for me.

 

[Peter_SSs]

Sorry but this time, its taking steps 5,

.. because last time that is exactly what you said you wanted ..

the ranges are A1:A5,A6:A10,A11:A13.... i want results in A1,A6,A10

Sub Concat_Cells_v3()
  With Selection
    .Cells(1).Value = Application.Trim(Join(Application.Transpose(.Value)))
  End With
End Sub

In case you might run it with only a single cell selected, it would be safer with the following.
In either case I have assumed you would have selected a single range in a single column

Sub Concat_Cells_v3()
  With Selection
    If .Count > 1 Then .Cells(1).Value = Application.Trim(Join(Application.Transpose(.Value)))
  End With
End Sub

 

[raghavakl]

.. because last time that is exactly what you said you wanted ..

Sub Concat_Cells_v3()
  With Selection
    .Cells(1).Value = Application.Trim(Join(Application.Transpose(.Value)))
  End With
End Sub

In case you might run it with only a single cell selected, it would be safer with the following.
In either case I have assumed you would have selected a single range in a single column

Sub Concat_Cells_v3()
  With Selection
    If .Count > 1 Then .Cells(1).Value = Application.Trim(Join(Application.Transpose(.Value)))
  End With
End Sub

Thanks a lot, worked
Regards

 

[Peter_SSs]

Good news! Glad we got there in the end.