Concatenate many cells without zero's
I dropped your test for 0.
Option Explicit
Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange.Cells
if vartemp.value < 0 then
JoinString = JoinString & varDelimiter & varTemp.Text
end if
Next varTemp
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, Len(varDelimiter) + 1)
End If
End Function
Dave Peterson wrote:
If you've the cells to be concatenated are formatted nicely, you could use this:
Option Explicit
Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange.Cells
JoinString = JoinString & varDelimiter & varTemp.Text
Next varTemp
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, Len(varDelimiter) + 1)
End If
End Function
art wrote:
Thanks. Can you please also modify the code so it gives you all the amount in
a number format like 9.00 and not 9?
Thanks.
Art
"Jacob Skaria" wrote:
Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
If varTemp < 0 Then
JoinString = JoinString & varDelimiter & varTemp
End If
Next
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, 2)
End If
End Function
--
If this post helps click Yes
---------------
Jacob Skaria
"art" wrote:
I had a question on how to do the following:
Is there any easy way to concatenate many cells to one cell. I have row from
A1:P1 with information in them. I want to connect them together in one long
string. Is there an easier way to do this, other than using the formula
concatenate and inserting each cell, or the "&" function? (it is to
cumbersome and I think excessive)
I got a response from "Jacob Skaria":
Why not use a UDF..Launch VBE using short-key Alt+F11. Insert a module and
paste the below code..
A1= "To be"
B1 = "merged"
C1 = JoinString(A1:B1," ")
Function JoinString(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
JoinString = JoinString & varDelimiter & varTemp
Next
If varDelimiter < vbNullString Then
JoinString = Mid(JoinString, 2)
End If
End Function
However, I have some cells that have zero values in them Is there
a way to change the VBA code to ignore the zero values. So for example, I
want to use the formula like this,
A1 B1 C1 D1 E1
F1 G1
1.29 2.29 3.39 4.99 0 0
0
JoinString(A1:G1,", "), it should give me: 1.29, 2.29, 3.39, 4.99
And that's it, no zero values.
Thank you for your prompt response.
Art.
--
Dave Peterson
--
Dave Peterson
|