excelvba

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


I have this sheet:

enter image description here

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:

enter image description here

How can I achieve this?


Solution

  • 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
    

    enter image description here