VBA concatenate cells

[Vbanoob98]

Hi everyone!

Im looking to concatenate the active cell with the first cell of the column, separated by a coma.

So if active cell is A3 then concatenate A3 with A1, if active cell is B3 then concatenate with B1, if active cell is G30 concatenate with G1, etc

A1= January
Active cell = empty "run macro" active cell = January

B1 = July
Active cell = 7 "run macro" Active cell = 7,July

Any help woud be greatly appreciated

 

[JackDanIce]

Try:

Sub M1()

With ActiveCell
  .Value = .value & "," & activesheet.cells(1, .column).value
End With

End Sub

 

[Vbanoob98]

Genius! Thank you so much!

 

[JackDanIce]

You're welcome

 

[mcgee1505]

Hi, I have a similar problem where I have first five cells in the row to concatenate but with a - between each, then carry on down the sheet till last row, can this be done with VBA also?

 

[alansidman]

Alternative to VBA is Power Query/Get and Transform. Here is Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Source,{"Column1", "Column2", "Column3", "Column4", "Column5"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

 

[mcgee1505]

Thank You. I will look at Power Query now

 

[Peter_SSs]

Hi, I have a similar problem where I have first five cells in the row to concatenate but with a - between each, then carry on down the sheet till last row, can this be done with VBA also?

Does this do what you want?

Sub Textjoin_Values()
  With Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=TEXTJOIN(""-"",1,A2:E2)"
    .Value = .Value
  End With
End Sub

20 08 11.xlsm
	A	B	C	D	E	F
1
2	a	d	g	j	m	a-d-g-j-m
3	b	e	h	k	n	b-e-h-k-n
4	c	f	i	l	o	c-f-i-l-o
5
Concat

 

[Vbanoob98]

deleted