PDA

View Full Version : Make the range dynamic



Ole Kristen.
11-22-2016, 12:01 PM
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
11-22-2016, 05:02 PM
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.
11-22-2016, 11:18 PM
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
11-23-2016, 02:40 AM
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
11-23-2016, 05:55 AM
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)