If Cell is empty, copy entire row above and insert into empty cell's row

Question

So we’re developing a new tracking tool. In the first part of my Macro, if there is a quantity in column M greater than 1, it inserts that many rows (minus 1) underneath. We are trying to isolate each quantity with its own row. So instead of 1 row quantity 9, it shows 9 rows quantity 1.

What I need now is after the first part of the Macro runs, if a cell in Column A is blank, copy row from above and insert into empty cell’s row. I know how to do this on a cell by cell basis in VBA (see below), but there are a lot of blank cells that should be blank.

Fill all empty cells with cell from above will not work because some cells need to be blank. The indicator needs to be in column A and the entire row above needs to be copy and pasted into empty cell row if cell in column A is blank.

Sub Fill()

Dim lr As Long
lr = Cells(Rows.count, 1).End(xlUp).Row
On Error Resume Next
Range("A4:M" & lr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

End Sub

Darrell H Darrell H · Accepted Answer · 2019-08-02T21:41:15

You can test the cell in column A to see if it is empty, then copy the row above. Might be a more efficient way of doing it, but this works

Sub Fill()
    Dim lr As Long
    Dim x As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    For x = 2 To lr
        If IsEmpty(Cells(x, 1)) Then
            Cells(x - 1, 1).EntireRow.Copy Cells(x, 1)
        End If
    Next x
End Sub

If Cell is empty, copy entire row above and insert into empty cell's row

1 Answers