Hi,

I have posted this problem before and has receive the answer required then. But now I need something more.

I have a very large sheet of data, I need a loop to go through a particular column in this case the year column. Here when the value is 2009 the code will delete the row. Then when it reaches cell with value of 2014 it will then insert a row and the cell value will be 2015 and the cell value below will be changed to 2021.

This is the code I currently have which was provided by rylo and I would like to tweak it such that after the row is being inserted for year 2015 I want to copy the data of adjacent to 2014 down to 2015. i have attached a sample file with the results i am looking for. 

Sub yearInsertion()
    
    Dim i As Integer
    Dim j As Integer    
    
    j = 3    

    'find the range of data
    For i = Cells(Rows.Count, j).End(xlUp).Row To 2 Step -1
    
        If Cells(i, j) = 2020 And Cells(i - 1, j) = 2014 Then

            Cells(i, j).EntireRow.Insert shift:=xlDown

            Cells(i, j).Value = 2015

            Cells(i + 1, j).Value = 2021

        ElseIf Cells(i, j).Value = 2009 Then

            Cells(i, j).EntireRow.Delete shift:=xlUp
        End If
    Next i

        If Cells(1, j) = 2009 Then
            Cells(1, j).EntireRow.Delete shift:=xlUp
        End If
End Sub
Sample.xlsx