Calculate non-empty rows for concatenation

Hello,

So I am having a bit of trouble figuring this out.

What I want to do is calculate how many rows have data in column A (which I have been able to do), but then using that info I then want to concatenate that data with a ", " in between each value (which is where I am stuck). So the VBA should calculate say 3 values (last value in A3, first data always in A1), then concatenate those values (v1, v2, v3) to be copied and special pasted (just values into a selected cell on another sheet) without needing to hard code the formulas on the worksheet.

For the time being I have physically typed in the concatenate formulas (for 2 values, 3, etc up to 6 values - since there is usually not more than 6 for now) and manually select the row with the correct amount of values.

Any help would be greatly appreciated as it would make this project immensley easier.

Thanks,

-BC

See if this workbook gives you some ideas.

That is a VBa solution, just used as a UDF. it isn't a standard Excel Formula.

Why not post a sample workbook showing your typical data and how you need it to end up?
A picture with only 3 or 4 rows is not enough to explain your problem clearly.

Sorry, I consider myself an intermediate noob with VBA and was not sure what UDF was.

Please bear with me.

If you could spell out precisely what I need to do it would help me a lot. I thought this would be pretty simple to achieve but it is proving more complicated than I imagined.

My issue is that there could be 2 values, 3, or perhaps even 6. I want to write a macro that will calulate the number of values in Coulmn A and concatenate them with a comma and space the be pasted in column D on the first sheet. And again it will not always be the same number of values to concatenate.

Perhaps if you can post the actual vba code you use it may clear up my confusion, I do see the vba function now,so I apologize for that.

So it seems my question is how to calculate and pass the varying range to the function.


I really do appreciate the help not trying to make you mad, it's just the noob in me.


-BC

P.S. So I made a copy of the actual sheet I am using (with my ghetto fabulous solution). 1. I select the cell in column E on the first page.
2. I paste in the multiple values on the second sheet in Column A
3. They are concatenated in column F
4. I select the row with the correct number of values in F
5. Run my little macro (ctrl+f) to paste values into the initial cell on the 1st sheet.

Would love if I could automate steps 3-5, after pasting the values just run a macro that will concatenate the proper number of values and paste them right into the first sheet.

I hope that this will make my thoughts a bit clearer.

Here is what I tried to get a draggable formula.

In a standard module
Option Explicit

Function Conc(RangeAddress As String, Optional Separator As String = "")
    Dim Cell As Range, rng As Range
    
    Set rng = Range(RangeAddress)
    For Each Cell In rng
        If Cell <> "" Then
            ConcBeforeBlank = ConcBeforeBlank & Separator & Cell
        End If
    Next
    If Separator <> "" Then
        ConcBeforeBlank = Right(ConcBeforeBlank, Len(ConcBeforeBlank) - Len(Separator))
    End If
End Function

Put this in A2 (With your Data in Column B starting Row 2)

=IF(B2="","",IF(ISNUMBER(A1),1+A1,1))

Drag/Fill Down

In D2

=IF(B2="","",IF(B3="",concBeforeBlank("B"&ROW()-A2+1&":B"&ROW(),", "),""))

Drag/Fill Down

This will return a concatenation of the rows that are separated by blanks.

Hope this helps.

or

Sub snb()
 For Each ar In Sheets("Multiple paste Values").Columns(2).SpecialCells(2).Areas
  ar.Cells(ar.Cells.Count).Offset(, 2) = Join(Application.Transpose(ar), ",")
 Next
End Sub