VBA insert row and fill cell with date minus 1 from cell below

Question

I have this sheet:

Colum A has a date. What I need is to insert a row above this first row (which is not the first row in the sheet) This inserted row must be empty except the date which must be one day before the current start date (so 2021/08/18)

Like this:

How can I achieve this?

Answer 1

• Rows().Insert insert a blank row, then populate the desired date

Note: You may need to revise the code depending on your table layout.

Microsoft documentation:

Range.Insert method (Excel)

Range.CurrentRegion property (Excel)

Sub Demo()
    Const FIRST_CELL = "A3"
    With ActiveSheet.Range(FIRST_CELL).CurrentRegion
        .Rows(2).Insert
        .Cells(2, 1) = .Cells(3, 1) - 1
    End With
End Sub