Macro merge cells separated by comma, ignore blank cells

Finally i have found a solution with this formula:

=IF(A1=0;"";A1&",") & IF(B1=0;"";B1&",") & IF(C1=0;"";C1)

Ok this is the new macro that works:

    Columns("AD:AD").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Range("AD2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-10]=0,"""",RC[-10]&"","") & IF(RC[-9]=0,"""",RC[-9]&"","") & IF(RC[-8]=0,"""",RC[-8]&"","") & IF(RC[-7]=0,"""",RC[-7]&"","") & IF(RC[-6]=0,"""",RC[-6]&"","") & IF(RC[-5]=0,"""",RC[-5]&"","") & IF(RC[-4]=0,"""",RC[-4]&"","") & IF(RC[-3]=0,"""",RC[-3]&"","") & IF(RC[-2]=0,"""",RC[-2]&"","") & IF(RC[-1]=0,"""",RC[-1])"
    Selection.AutoFill Destination:=Range("AD2:AD" & Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault
    Range("AD2:AD" & Range("A" & Rows.Count).End(xlUp).Row).Select

    Selection.Copy
    Range("AE2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    Range("AD:AD,AC:AC,AB:AB,AA:AA,Z:Z,Y:Y,X:X,W:W,V:V,U:U,T:T").Select
    Selection.Delete Shift:=xlToLeft

info

This Macro Merge Columns T,U,V,W,X,Y,Z,AA,AB,AC into AD Column and separates values by comma and ignores blank cells. After that it copies the results into AE Column and deletes the unnecessary Columns. So when the macro executes completely you have all this Columns merged into T Column

Heres an example:

IMAGE_1	IMAGE_2	IMAGE_3	MERGED_IMAGES
myimage1.jpg	myimage2.jpg	myimage3.jpg	myimage1.jpg,myimage2.jpg,myimage3.jpg
myimage1_b.jpg	myimage2_b.jpg	myimage3_b.jpg	myimage1_b.jpg,myimage2_b.jpg,myimage3_b.jpg
myimage1_c.jpg	myimage2_c.jpg	myimage3_c.jpg	myimage1_c.jpg,myimage2_c.jpg,myimage3_c.jpg

PS!!!

I have found a second problem, after the macro executes i have found some cells with an unneeded comma in the end of the cell value.
So i have create a second macro to fix that problem with the macro recorder and this formula

=IF(RIGHT(J2;1)=",";LEFT(J2;LEN(J2)-1);J2)

You can change the formula to whatever you want, eg if you want to remove the "w" character in the end of the value of A2 cell you can change the formulla like this:

=IF(RIGHT(A2;1)="w";LEFT(A2;LEN(A2)-1);A2)

Originally Posted by jmac1947

Hi lagiosman,

I work much better if I have an example spreadsheet (with the current macros). If you can post an e3xample of what you have and what you are looking to see as the result i will have a look and see if I can help

jmac

Thank you for your interest my friend

I have write a new macro and now it works, i have update my original post.