Join rows in to one cell

[Clamarc]

My excel 2016 doesn't have the "textjoin" function, so I use a custom function that I called "joinlines", where the values of several lines of a column are joined in a single cell and separated by comma+space. The problem is that if there are hidden rows they are also returned in the join result and I also need to remove the VCI- and PO- from the beginning of the records.

The values in the rows i want to join are like this:
VCI-2130/17
VCI-2134.1/17
PO-2134.2/17

In my function, I managed to join the values, but I have two problems:
1 - I don't want to join the hidden rows
2 - I don't want the values from the beginning, ex. VCI- and PO-

And I wanted to merge into a single cell like this, without the VCI- and PO- at the beginning and only on the visible rows:
2130/17, 2134.1/17, 2134.2/17

See the function below:

Public Function JOINLINES(vArray As Variant, _
Optional lSize As Long, _
Optional sSeparator As String = ", ") As Variant
Dim v As Variant
Dim lCount As Long
Dim asOut() As String
 
If lSize < 0 Then
JOINLINES = CVErr(xlErrNum)
Exit Function
End If
 
For Each v In vArray                                 '--.
If v <> "" Then                                            '| 
lCount = lCount + 1                                   '| here loads my array,
ReDim Preserve asOut(1 To lCount)           '| but i want to load [B]only[/B] 
asOut(lCount) = v                                       '| the [B]visible rows[/B] 
If lCount = lSize Then Exit For                    '| without the [B]VCI-[/B] and [B]PO-[/B]
End If                                                          '|
Next v                                                      '--.
 
JOINLINES = Join(asOut, sSeparator)
End Function

What should i change in the code to join only visible rows and the values without VCI- and PO- ?

 

[Clamarc]

hi DanteAmor,

OK, the code you posted above worked!

A curiosity... when we select cells individually, excel itself creates the selected parameters as (A1;A4;A12), ok?... is there any command that transforms this individual selection to ("A1,A4,A12")?

thanks for you help