IF loop for single rows and print values in one cell?

[Jimbo8678]

Hi,

First time posting here so hopefully I explain this well enough...

I have columns of data, some of which have no data in, some of which have a number such as 108-88-3.

What I would like to do is use the IF function, but then print the values of the cells in a single cell.

Sum Excel Query.PNG

Formula in Sum column is - =IF(E13<>0,""&E13&";","")&IF(F13<>0,""&F13&";","")&IF(G13<>0,""&G13&";","")&IF(H13<>0,""&H13&";","")

The formula above works in the way I want, however I have 500 column to do this for and so need a way to get excel to do the above for me!

Please ask me any questions if I haven't been clear!

[nigelog]

For that layout, returning from array rather than formula

Sub test()
Dim a, i, ii, str As String
a = [b1].CurrentRegion.Offset(1, 0)
    For i = LBound(a, 1) To UBound(a, 1)
     For ii = LBound(a, 2) To UBound(a, 2)
        If a(i, ii) <> "" Then str = str & "," & a(i, ii)
     Next ii
     Cells(i + 1, 1).Value = str: str = ""
    Next i
End Sub

Right click on sheet tab - "View code" - paste above code and press F5