Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate many cells without zero's
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate many cells without zero's
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate many cells without zero's
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate many cells without zero's
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate many cells without zero's
Your code gives me the same results as 9.2 and not 9.20. Thanks for your help.
Art. "Dave Peterson" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate many cells without zero's
Actually, it gives you want's displayed in the cell on the worksheet. If you
change the format to show what you want, you can use that code. If you don't want to change the cell's format, you can do the same thing in code: JoinString = JoinString & varDelimiter & format(varTemp.value,"0.00") Use the format that you want--include as many decimals, leading zeros, commas, currency symbols as you want. art wrote: Your code gives me the same results as 9.2 and not 9.20. Thanks for your help. Art. "Dave Peterson" wrote: 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate many cells without zero's
Right. Got it. But now it formats it so there is a space between each number
7 spaces. What do I have to change so there is no or only one space in between. (really, the best should be what ever I put in the formula, like =joinstring(A1:A10,", ") and thats it). Thanks for your help. Art. "Dave Peterson" wrote: Actually, it gives you want's displayed in the cell on the worksheet. If you change the format to show what you want, you can use that code. If you don't want to change the cell's format, you can do the same thing in code: JoinString = JoinString & varDelimiter & format(varTemp.value,"0.00") Use the format that you want--include as many decimals, leading zeros, commas, currency symbols as you want. art wrote: Your code gives me the same results as 9.2 and not 9.20. Thanks for your help. Art. "Dave Peterson" wrote: 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 -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate many cells without zero's
What version of the function are you using?
What's in A1:A10? art wrote: Right. Got it. But now it formats it so there is a space between each number 7 spaces. What do I have to change so there is no or only one space in between. (really, the best should be what ever I put in the formula, like =joinstring(A1:A10,", ") and thats it). Thanks for your help. Art. "Dave Peterson" wrote: Actually, it gives you want's displayed in the cell on the worksheet. If you change the format to show what you want, you can use that code. If you don't want to change the cell's format, you can do the same thing in code: JoinString = JoinString & varDelimiter & format(varTemp.value,"0.00") Use the format that you want--include as many decimals, leading zeros, commas, currency symbols as you want. art wrote: Your code gives me the same results as 9.2 and not 9.20. Thanks for your help. Art. "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate many cells without zero's
Oh right. Sorry, I forgot about the last part you gave me. JoinString =
JoinString & varDelimiter & format(varTemp.value,"0.00"). Thanks so much. It works great. Thanks Dave. Art. "Dave Peterson" wrote: What version of the function are you using? What's in A1:A10? art wrote: Right. Got it. But now it formats it so there is a space between each number 7 spaces. What do I have to change so there is no or only one space in between. (really, the best should be what ever I put in the formula, like =joinstring(A1:A10,", ") and thats it). Thanks for your help. Art. "Dave Peterson" wrote: Actually, it gives you want's displayed in the cell on the worksheet. If you change the format to show what you want, you can use that code. If you don't want to change the cell's format, you can do the same thing in code: JoinString = JoinString & varDelimiter & format(varTemp.value,"0.00") Use the format that you want--include as many decimals, leading zeros, commas, currency symbols as you want. art wrote: Your code gives me the same results as 9.2 and not 9.20. Thanks for your help. Art. "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display Zero's (0) in blank cells | Excel Programming | |||
zero's in linked cells | Excel Discussion (Misc queries) | |||
Adding zero's to a group of cells | Excel Discussion (Misc queries) | |||
Average Non-Continuous Cells Without Zero's | Excel Worksheet Functions | |||
Average non continguous cells, excluding zero's | Excel Worksheet Functions |