Help : Covert a sub code to function code

[anishms]

Hi,
Is it possible to convert the below sub to a function

Option Explicit

Sub MergeRows()
    Const Ref1 As String = "E"
    Const Ref2 As String = "F"
    Const Output As String = "J"
    Const Delimiter As String = " "
    Const RowFirst As Long = 3
    Const RowLast As Long = 62
    
    Dim x      As Long, y As Long, n As Long, z As Long
    Dim Items() As String

    For x = RowFirst To RowLast
        If IsEmpty(Cells(x, Ref1)) Then
            y = Range(Ref1 & x, Range(Ref1 & x).End(xlDown)).Rows.Count - 1
            ReDim Items(0 To y)
            z = 0
            For n = 0 To y
                If Not IsEmpty(Range(Ref2 & x).Offset(n - 1, 0)) Then
                    Items(z) = Range(Ref2 & x).Offset(n - 1, 0).Value
                    z = z + 1
                End If
            Next n
            ReDim Preserve Items(0 To z - 1)
            Cells(x, Output).Offset(-1) = Join(Items, Delimiter)
            x = x + y
        End If
    Next x
End Sub

 

[p45cal]

Not exactly what you asked for but using built-in functions in cell J3:

=IF(AND(LEN(E3)>0,LEN(E4)=0),TEXTJOIN(" ",TRUE,F3:INDEX(F3:F$62,MATCH(FALSE,ISBLANK(E4:E$62),0))),"")

copied down to all but the very last row?

 

[anishms]

Not exactly what you asked for but using built-in functions in cell J3:

=IF(AND(LEN(E3)>0,LEN(E4)=0),TEXTJOIN(" ",TRUE,F3:INDEX(F3:F$62,MATCH(FALSE,ISBLANK(E4:E$62),0))),"")

copied down to all but the very last row?

Wow, Thanks p45cal
Its insightful using index and match
I was initially trying with IFS function but couldn't succeed due to the length of blank cells are not constant.