Concatenate above cells into the blank cell below

Hi, I am trying to have replace the blank cell (which is essentially a subtotal cell) with a concatenation of the above cells. Unfortunately there is a random number of cells above each blank. se...

excel

Formulas and Functions

Macros and VBA

HansVogelaar

May 26, 2023

IT990

Select the range, including the blank cell at the end.

Then run this macro:

Sub FillTheBlanks()
    Dim r1 As Long
    Dim r2 As Long
    Application.ScreenUpdating = False
    r1 = 1
    For r2 = 2 To Selection.Rows.Count
        If Selection.Range("A" & r2).Value = "" Then
            Selection.Range("A" & r2).Value = Application.TextJoin(", ", _
                True, Selection.Range("A" & r1 & ":A" & r2))
            r1 = r2 + 1
        End If
    Next r2
    Application.ScreenUpdating = True
End Sub

Sub FillTheBlanks() Dim r1 As Long Dim r2 As Long Application.ScreenUpdating = False r1 = 1 For r2 = 2 To Selection.Rows.Count If Selection.Range("A" & r2).Value = "" Then Selection.Range("A" & r2).Value = Application.TextJoin(", ", _ True, Selection.Range("A" & r1 & ":A" & r2)) r1 = r2 + 1 End If Next r2 Application.ScreenUpdating = True End Sub

Forum Discussion

Concatenate above cells into the blank cell below

Resources