Merge Cells Automatically using VBA

[Sefty]

Hello

I'm looking for something to automatically merge text of the text in a cell. This can either be immediately or by using VBA and running a macro to do all of them at once. Example for data merge data football, and I'm looking to merge by using VBA to data volly, basketball, and tenis.
Thanks....

 

[Colo]

Tried Peter's code on the data copied from XL2BB. Blank cells in column A aren't recognized as blank. (I'm not sure if this issue comes from XL2BB, or from his spreadsheet.) After making "those looks blank cells" blank by the Delete key, Peter's code works perfectly.

 

[Sefty]

For syntax errors the code usually turns red and the yellow line with arrow will not progress to the line in question like you have shown.

Instead of a Syntax error, are you sure that you are not getting an error like this instead?

View attachment 70203

From your sample, it appears that many of the "blank" cells are not blank (empty) but contain space characters. Could that be your problem?

yes alright I get error information like in the picture.
Wow that's right, there is a space in an empty cell. Now syntax can be used and it works.
Thank you very much....

 

[Peter_SSs]

I get error information like in the picture.

Always try to give the exact error message as well as the line that it occurs on.

Wow that's right, there is a space in an empty cell.

Not sure how they are getting there but the code could be adapted to remove them first if required.

Glad it is resolved anyway.

 

[Georgiboy]

I would duplicate the values rather than merge personally, you will see what I mean if you ever try to pivot the data.

 

[Sefty]

Hello,
sorry if i have case to merge cells automatically using VBA with the form of data as below, I want to merge data for cells N and the result is like in cells M.
Thanks...

 

[Peter_SSs]

and the result is like in cells M.

Do you mean cells O?

Are the values in column N the result of formulas or simply text values?

 

[Sefty]

Do you mean cells O?

Are the values in column N the result of formulas or simply text values?

Owh yes, I meas result like cells O

 

[Peter_SSs]

I asked two questions & you answered one of them.

 

[Sefty]

I asked two questions & you answered one of them.

Sorry I missed, result coloumn N is simply text, because I don't know if there is a formulas to merge. I want to merge data for cells N and the result is like in cells O with VBA.

 

[Peter_SSs]

Sorry I missed, result coloumn N is simply text,

Thanks.

Please remember XL2BB for sample data.

Try this

Sub MergeSports_v3()
  Dim rA As Range
  
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace(Replace("if(#=%,"""",#)", "#", .Address), "%", .Offset(-1).Address))
    For Each rA In Range(.Address).SpecialCells(xlBlanks).Areas
      rA.Offset(-1).Resize(rA.Rows.Count + 1).MergeCells = True
    Next rA
  End With
End Sub