Excel VBA: Cut & Paste Cell Text to Above, If Cell Value to Left = 0

Question

As per the title using VBA, I'm trying to cut & paste (adding commas) a text value (Column C) to the first cell above with a value (not blank), cutting only when the original cell's adjacent cell (Column B) is blank.

To demonstrate more succinctly, the following diagram (knowing the total rows is an unknown value) shows the starting point:

    ColumnA ColumnB ColumbC
Row1    a   b   c
Row2            d
Row3    j   k   e
Row4            f
Row5            g
Row6    l   m   h
Row7    n   o   i

With the following diagram being the outcome after the above:

    ColumnA ColumnB ColumbC
Row1    a   b   c, d
Row2            
Row3    j   k   e, f, g
Row4            
Row5            
Row6    l   m   h
Row7    n   o   i

Have you tried anything? Generally better here if you've made an effort and hit a block. — SJR

Kubie Kubie · Accepted Answer · 2018-09-04T14:23:34

You can iterate through each row and move the info up if col A is empty

Sub test()

    Dim nonEmptyRow As Long: nonEmptyRow = 1
    Dim lastRow As Long
    Dim row As Long

    With ThisWorkbook.Worksheets("insert ur sheet name")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For row = 1 To lastRow
            If Len(CStr(Trim(.Cells(row, "A").Value))) > 0 Then
                nonEmptyRow = row
            Else
                .Cells(nonEmptyRow, "C").Value = .Cells(nonEmptyRow, "C").Value & ", " & .Cells(row, "C").Value
                .Cells(row, "C").Value = ""
            End If
        Next
    End With

End Sub

EDIT code for reverse:

Sub test()

    Dim nonEmptyRow As Long
    Dim lastRow As Long
    Dim row As Long

    With ThisWorkbook.Worksheets(1)
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).row
        nonEmptyRow = lastRow

        For row = lastRow To 1 Step -1
            If Len(CStr(Trim(.Cells(row, "A").Value))) > 0 Then
                nonEmptyRow = row
            Else
                .Cells(nonEmptyRow, "C").Value = .Cells(nonEmptyRow, "C").Value & ", " & .Cells(row, "C").Value
                .Cells(row, "C").Value = ""
            End If
        Next
    End With

End Sub

Excel VBA: Cut & Paste Cell Text to Above, If Cell Value to Left = 0

1 Answers