Cobining more than 30 columns of data with concatenate.

J

JT

I have about 50 columns of data I would like to combined to one column, if
Text, if cell is equal to 0 or blank than I do not want to pull into the
cell. How can this be done ?
Also how do I combine mulitple rows of data, if criteria is met in function?
I have qualifiers, to determine if rows should combine
 
J

JE McGimpsey

One way, using a UDF:

Public Function ConCatNonZero(rng As Range) As String
Dim rCell As Range
Dim sTemp As String
Dim sAdd As String
For Each rCell In rng
sAdd = rCell.Text
If Len(sAdd) > 0 Then _
If IsNumeric(sAdd) Then _
If CLng(sAdd) = 0 Then sAdd = ""
sTemp = sTemp & sAdd
Next rCell
ConCatNonZero = sTemp
End Function

Call as

=ConCatNonZero(A1:AX1)
 
J

JT

Not sure how to do what you describe below? Do I go into the view code
window and copy paste?
 
O

Ola

Do like this,
1. Press Alt+F11 to get to the VBA Window
2. Goto to menu: Insert>Module
3. Copy and paste the code (you might need to fix some loose row breakes or
spaces)
4. Write the Formula in your worksheet: =ConCatNonZero(A1:AX1) where it
should be
Done

Ola Sandström
 
O

Ola Sandström via OfficeKB.com

It looks simple but it can't get it to work either. --> #VALUE!
 
J

JE McGimpsey

Then you're either putting it in another workbook, or you're putting it
in the ThisWorkbook module, a worksheet module or a class module.
 
Top