How to use activecell row and column in VBA function with filldown?

Question

I wrote a function which will concatenate all the cells to the left of the cell the function is in, using a delimiter. My code is:

Public Function Concat_To_Left(delim As String)
    Dim C, R As Long
    Dim S As String
    Dim Cell As Range
    Set Cell = ActiveCell
    C = Cell.Column
    R = Cell.Row
    S = Cells(R, 1).Value
    For i = 2 To (C - 1)
        S = S & delim & Cells(R, i).Value
    Next i
    Concat_To_Left = S
End Function

This code works if calculating a single row. The problem I'm running into is that the cell.row and cell.column seem to be saved from the first cell when I fill the function to the bottom of a column (by double clicking the bottom right of the cell in the excel sheet). This results in all cells with this function having the same value as the cell being filled down from.

Screen-Updating, Events, and Alerts are all on/true. Application.Calculation is set to xlCalculationAutomatic

Can anyone tell me how to make this function work on each cell the formula is filled down into, using the proper row and column for each cell (not that column matters when filling down)?

But realize this is not volatile and will not change if the cell to the left change. — Scott Craner, Jan 31 '22 at 23:36
BTW, If you have TEXTJOIN you can do this with a formula: `=TEXTJOIN(", ",TRUE,B2:INDEX(2:2,COLUMN()-1))` If older version here is a function that mimics textjoin: https://stackoverflow.com/questions/42165179/ms-excel-concat-with-a-delimiter/42165195#42165195 — Scott Craner, Jan 31 '22 at 23:39
@ScottCraner I did not know about `=TEXTJOIN`. Your formula worked perfect. The code you linked for the function is great as well. I may try to modify that to not need the boolean or range value, which is what I was trying to achieve with my simple code. — Sabatino Ognibene, Jan 31 '22 at 23:46
By removing the range, you make it so it will not recalc when the data changes. You will need to add `Application.Volatile` as the first line so it recalcs with every change. — Scott Craner, Jan 31 '22 at 23:47
@ScottCraner, please post your comment about textjoin as the answer. Thank you. — Sabatino Ognibene, Feb 01 '22 at 15:33

Sabatino Ognibene · Answer 1 · 2022-02-02T17:06:45.443

Scott's comment about using TEXT join worked as a workaround to what I was trying to accomplish.

=TEXTJOIN(", ",TRUE,B2:INDEX(2:2,COLUMN()-1))

The link he provided to the custom code for TEXTJOIN was very nice as well: MS Excel - Concat with a delimiter

Adding Application.Volatile did not make my function work. I did not find a way to get my function working with fill down without needing a range parameter, so TEXTJOIN is the next best option and answers my question for now.

EDIT:

I wrote this macro to work instead of a function:

Private Sub Concat_To_Left()

    Dim C, R, LR As Long
    Dim Cell As Range

    LR = ActiveWorkbook.ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    C = ActiveCell.Column
    R = ActiveCell.Row
    
    For Each Cell In ActiveWorkbook.ActiveSheet.Range(Cells(R, C), Cells(LR, C))
        Cell.Value = Cells(Cell.Row, 1).Value
        For i = 2 To (C - 1)
            Cell.Value = Cell.Value & "|" & Cells(Cell.Row, i).Value
        Next i
    Next Cell
    
End Sub

This one uses "|" as a delimiter, fills down from the active cell to lastrow concatenating every cell to the left, including blanks.

How to use activecell row and column in VBA function with filldown?

1 Answers1