Insert Rows Where Column Value Changes

[Neild137]

Before you flag this, please note that I need to amend Existing code to be able to fit multiple cases. I have:

Sub InsertRowAtChangeInValue() 
    Dim lRow As Long 
    For lRow = Cells(Cells.Rows.Count, "AN").End(xlUp).Row To 2 Step -1 
        If Cells(lRow, "AN") <> Cells(lRow - 1, "AN") Then Rows(lRow).EntireRow.Insert 
    Next lRow 
End Sub

I need to amend this to add the following functionality:

1. Rows need to be added in worksheet titled "Location File Data", with the date reference column as the qualifier being "AN".
2. Be able to let me specify what the previous value to look for and next value changed to should be. Insert rows where this change happens. IE, there are rows of data for 2017, 2016, 2015, 2014 and 2013 respectively. I want the code to let me specify whether to look for the spot where 2017 data ends and 2016 begins, or where 2015 ends and 2014 begins, etc... I would rather this not be by prompt, but rather by a reference to a cell in a sheet titled "Compare" which contains the number of rows to insert

 

[Snakehips]

Neild137,

AN will have dates that you wish to reference by year ? AN will be sorted ascending or descending?

 

[Neild137]

Yes, correct. AN has dates, like 3/31/17, 12/31/16, 12/31/15 and these are in descending order, starting with 2017, ending with 2013 at the bottom

 

[Snakehips]

See if this helps.

Compare

*	A	B	C
1	Insert Above	Rows	At Row
2	2017	2	4

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:101px;"><col style="width:64px;"><col style="width:75px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C2 {=IFERROR(MATCH(A2,YEAR('Location File Data'!AN1:AN1002),0),"No Match")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Sub InsertRowAtChangeInValue()
    Dim myRng As Range
    Dim atRow, numRows As Long
    
    If Sheets("Compare").Range("C2") = "No Match" Or Sheets("Compare").Range("B2") <= 0 Then Exit Sub
    
    atRow = Sheets("Compare").Range("C2")
    numRows = Sheets("Compare").Range("B2") - 1
    
    Set myRng = Sheets("Location File Data").Range("AN" & atRow & ":AN" & atRow + numRows)
    
    myRng.EntireRow.Insert
    
End Sub

You enter Year to insert above in A2 Number of rows in B2
Array Formula in C2 computes the start row.

You need to edit AN range depth to suit.
Formula can be tweaked if you prefer to enter a year to insert below.

 

[Neild137]

This works great. Thank you so much.