TextJoin copy formula all the way down to existing values on left automatically

strat919

Board Regular
Joined
May 15, 2019
Messages
54
Hey..... I'm recording a macro with =TEXTJOIN(",",TRUE,A1:B1) and the result is correct in C1. Then I double-click and get the results all the way down till there is no more data. Then I stop macro. I look at the vba code and the range is set to that particular macro.

Problem is when I use the macro for another set of different length columns, the macro already has the range set.

Can I enter something different in the A1:B1 to just tell it to give the result all the way to end of column till the data ends?

I have many sets of A B columns and want to include this macro inside of 6 combined macros to automate.

Do I need a custom vba macro.... which I don't know how to do.

Also I need it to replace the results in C column with unique values and column A and B deleted. I guess all that can be done with a custom vba macro if TEXTJOIN cannot be used in a macro I record myself.

I want to thank Yongle, Michael M, and Irobbo314 for their help on the other macros:)

Any help much appreciated
 
Last edited:

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Post your recorded Macro so that it can be amended to allow for the last row to be set as a variable.
 
Upvote 0
How about
Code:
Sub strat919()
   With Range("C1:C" & Range("A" & Rows.Count).End(xlUp).Row)
      .Formula = "=TEXTJOIN("","",TRUE,A1:B1)"
   End With
End Sub
 
Upvote 0
Code:
Sub MergeAddComma()
'
' MergeAddComma Macro
'

'
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=TEXTJOIN("","",TRUE,RC[-2]:RC[-1])"
    Range("C1").Select
    Selection.AutoFill Destination:=Range("C1:C41")
    Range("C1:C41").Select
    Columns("C:C").Select
    Selection.Copy
    Columns("C:C").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:B").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
End Sub
 
Upvote 0
Thanks Fluff:) ... but if I record an additional macro selecting column C copying and pasting special, values, deleting column A and B, I end up getting a specific range again. I guess it needs to be included also in the code.
 
Upvote 0
I tried this..... it works, but all of column A is selected after operation which interferes with my next macro. Column A needs to be selected only to last cell with a value. The entire result should have only A column selected with results.

Code:
Sub MergeAddComma()
   With Range("C1:C" & Range("A" & Rows.Count).End(xlUp).Row)
      .Formula = "=TEXTJOIN("","",TRUE,A1:B1)"
   Columns("C:C").Select
    Selection.Copy
    Columns("C:C").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:B").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft

   End With
End Sub
 
Last edited:
Upvote 0
How about
Code:
Sub strat919()
   With Range("C1:C" & Range("A" & Rows.Count).End(xlUp).Row)
      .Formula = "=TEXTJOIN("","",TRUE,A1:B1)"
      .Value = .Value
   End With
   Columns("A:B").Delete
End Sub
 
Upvote 0
Thanks Fluff.... that worked perfectly

I tested the code I merged from previous post above and it doesn't seem to interfere with the next macro so I'm probably good to go on that. The problem is with the next macro. I have to set the range before I run it. If it would only create the amount of cells needed to complete the macro. It works well and will give results to multiple sheets, sometimes I'm getting millions of results that exceeds the row limitations. If I set it to Const maximum = 1048576 to handle any situation, it creates so many rows if not needed, and is hard to work with if I'm working with a smaller amount of rows. Thanks

Code:
Public Sub GetUniquePairs()
    Application.ScreenUpdating = False
    Const maximum = 1048576
    Dim lastRow As Long, thisRow As Long
    Dim i As Long, j As Long
    Dim ws As Worksheet, Results As Worksheet
    Dim Res(1 To maximum, 1 To 1) As Variant
    
    Set ws = ActiveSheet
    Set Results = Sheets.Add
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    thisRow = 1
    
    For i = 1 To lastRow - 1
        For j = i + 1 To lastRow
            c = c + 1
            Res(thisRow, 1) = ws.Cells(i, 1).Value & "," & ws.Cells(j, 1).Value
            thisRow = thisRow + 1
                If thisRow = maximum Then
                    thisRow = 1
                    Results.Cells(1, 1).Resize(maximum).Value = Res
                    Erase Res
                    Set Results = Sheets.Add
                End If
                If i = lastRow - 1 Then
                    Results.Cells(1, 1).Resize(maximum).Value = Res
                    Erase Res
                End If
        Next j
    Next i
End Sub
 
Last edited:
Upvote 0
As this is a totally new question, you will need to start a new thread.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top