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:

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,675
Office Version
2019
Platform
Windows
Post your recorded Macro so that it can be amended to allow for the last row to be set as a variable.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,424
Office Version
365
Platform
Windows
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
 

strat919

Board Regular
Joined
May 15, 2019
Messages
54
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
 

strat919

Board Regular
Joined
May 15, 2019
Messages
54
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.
 

strat919

Board Regular
Joined
May 15, 2019
Messages
54
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,424
Office Version
365
Platform
Windows
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
 

strat919

Board Regular
Joined
May 15, 2019
Messages
54
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,424
Office Version
365
Platform
Windows
As this is a totally new question, you will need to start a new thread.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,943
Messages
5,483,821
Members
407,415
Latest member
Anton1999

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top