CODEWITHSUNDEEP
vbaexcel
Jalkey
Jalkey

Reputation: 15

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

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

Upvotes: 0

Views: 609

Answers (1)

Kubie
Kubie

Reputation: 1571

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

Upvotes: 1

Related Questions