VBA - insert rows after last occurring value

Question

Hello all,

Need a bit of help here!

I have two sheets (Sheet 1 and 2) - on sheet 1 there is a list of codes in A:A (with multiple occurrences of each code), on sheet 2 I have column A which contains selected codes that I have inputted.

I want a macro to insert a new row after the last instance of certain codes (on sheet 1) specified by me in A:A on sheet 2.

Currently using this:

Public Sub insert_rows()

Dim EndRow1 As Integer

Dim lastcellno1 As String

Dim findvalue1 As String

findvalue1 = Worksheets("Sheet2").Range("A2").Value

EndRow1 = Worksheets("Sheet1").Range("A:A").Find(what:=findvalue1, after:=Worksheets("Sheet1").Range("A1"), searchdirection:=xlPrevious).Row

lastcellno1 = "A" & EndRow1 + 1

Worksheets("Sheet1").Range(lastcellno1).EntireRow.Insert

End Sub

but this only inserts a row after the code found in cell A1 on sheet 2.  Also a bit cobbled together and could probably use some streamlining!

Any ideas on the best way to achieve this please?

thanks very much!

Jack

Answer 1

Please try with below code

Public Sub insert_rows()

    Dim lastrow As Long

    Dim EndRow1 As Long

    Dim lastcellno1 As String

    Dim findvalue1 As String

    lastrow = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To lastrow

        findvalue1 = Worksheets("Sheet2").Range("A" & i).Value

        EndRow1 = Worksheets("Sheet1").Range("A:A").Find(what:=findvalue1, after:=Worksheets("Sheet1").Range("A1"), searchdirection:=xlPrevious).Row

        lastcellno1 = "A" & EndRow1 + 1

        Worksheets("Sheet1").Range(lastcellno1).EntireRow.Insert

    Next i

End Sub

Answer 2

Absolutely perfect - thanks very much!

Answer 3

Hello Karthick,

Yes correct, it is working.

However I want to be able to type several codes in A:A on sheet 2, and have the macro insert 1 new row after the last instance of each code on sheet 1.

Look forward to your thoughts!

thanks

Jack

Answer 4

Hi Jack,

What you currently using is working and its inserting after the row of last instance in Sheet 1