Make the range dynamic

[Ole Kristen.]

Hello

I have the following code:

ActiveCell.FormulaR1C1 = "=TEXTJOIN("" "",TRUE,R[1]C:R[8]C)"
    ActiveCell.Select
    ActiveCell.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Selection.ClearContents
    ActiveCell.Offset(2, 0).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
End Sub

I need the range for the function TEXTJOIN to be dynamic. How do I do that?

[Leith Ross]

Hello Ole Kristen,

Currently, you are using the active cell and concatenating the next eight rows that are not blank. What do you want to change?

[Ole Kristen.]

Instead of taking the next eigth it should take the number of cells after eachother that contain samething. So, if there are ten or seven cells after eachother the function concate them and not only eigth of them.

[mana]

Option Explicit

Sub test()
    Dim r As Range
    Dim s As String
    
    With ActiveCell
        set r = Range(.Offset(1), .End(xlDown))
        s = TEXTJOIN(" ", True, r)
        r.ClearContents
        .Offset(1).Value = s
    End With
    
End Sub

[KevO]

mana - Unfortunately TextJoin is not available directly to VBA
perhaps replace

s = TEXTJOIN(" ", True, r)

EITHER with

s = WorksheetFunction.TextJoin(" ", True, r)

OR else with the 2 lines

s = "=TEXTJOIN("" "",TRUE," & r.Address(0, 0) & ")"
s = Evaluate(s)