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 concatenate() Dim i, j As Long Dim str As String i = Range("A" & Rows.Count).End(xlUp).Row + 1 For j = 1 To i If Cells(j, 1).Value <> "" Then Cells(j, 2).Value = Cells(j, 1).Value If str = "" Then str = Cells(j, 1).Value Else str = str & ", " & Cells(j, 1).Value End If Else Cells(j, 2).Value = str str = "" End If Next j End Sub

Forum Discussion

Concatenate above cells into the blank cell below

Resources