How to search for specific cell value and concatenate with row above

Question

I have an Excel spreadsheet with house addresses that are inconsistent. I would like to write vba code that searches for the word "UNIT" in column A and then concatenates it with the address row above. I'm sure this is way off but something like:

  sub ConcatenateRowAbove()
    Dim aCell as Range
    Dim lrow as Range
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
 'find last row in column A which has data

    lrow = .range("A" & Rows.Count).End(xlUp).Row

'loop through each cell in column A and find text "UNIT" then concatenate with address in row above

    For Each aCell in .Range("A" & lrow)

       If acell.value = "UNIT" Then _

       aCell.FormulaR1C1 = "=CONCATENATE(R[-1]C[1])"

    Next aCell

      End With

      End Sub

Example:

101 E Main St

UNIT 1

102 E Main St

103 E Main St

104 E Main St

UNIT 1

104 E Main St

UNIT 2

RESULTS: Column B would show as Concatenated IF there is a Unit Address:

101 E Main St, UNIT 1

102 E Main St

103 E Main St

104 E Main St, UNIT 1

104 E Main St, UNIT 2

Unknown Unknown · Accepted Answer · 2018-04-22T22:13:59

Loop bottom-to-top, concatenate then delete.

Dim lrow as long, i as long
Dim ws As Worksheet

Set ws = ThisWorkbook.workSheets("Sheet1")

With ws
    'find last row in column A which has data
    lrow = .range("A" & .Rows.Count).End(xlUp).Row

    'loop through each cell in column A and find text "UNIT" then concatenate with address in row above

    For i=lrow to 2 step -1
        If lcase(left(.cells(i, "A").value2, 4)) = "unit" Then
            .cells(i-1, "A") = .cells(i-1, "A").value2 & " " & .cells(i, "A").value2
            .rows(i).entirerow.delete
        end if
    next i
End With

How to search for specific cell value and concatenate with row above

1 Answers