Adjust the last row of a range as new rows are inserted

[angel34]

This macro looks for matches in ranges S9:S16 and D9:D62 and if a match is not found, copies a portion of the row

to the 1st available blank row of the Range(D9:D62). Similarly, in case a match is found, inserts a row below the

matched value and copies the same range to the newly inserted row. For example, if S10 and D16 is a match, a new

row is inserted below D16 and Range (S16:W16) is copied in the newly inserted row. There can be multiple matches

where multiple rows are inserted. This code works, but it seems to be unable to dynamically account for the newly

inserted rows and subsequently unable to determine the last row. As a result, depending upon the number of rows

inserted, the code overwrites few last rows of the range.

I shall be most thankful if someone guides to me to a solution to the above problem.

Sub Sort()

Dim e As Range
Dim foundVal As Range
Dim LR As Long
LR = Range("C" & Rows.Count).End(xlUp).Row

For Each e In Range("S9:S16")
Set foundVal = Range("D9:D62").Find(e, LookIn:=xlValues, lookat:=xlWhole)
If foundVal Is Nothing And e.Offset(, 6) = "Out of Stock"
Range("S" & e.Row & ":W" & e.Row).Copy Range("D" & LR + 1)
LR=LR+1
ElseIf Not foundVal Is Nothing And e.Offset(, 6) = "Available" Then
Range("D" & foundVal.Row).Offset(1).EntireRow.Insert
Range("S" & e.Row & ":W" & e.Row).Copy foundVal.Offset(1)
End If
Next e

End Sub

 

[shknbk2]

I added an extra LR=LR+1 line to the ElseIf block. The problem is that LR stayed the same even though a row was inserted. However, inserting a new row shifts the last used row down one as well.

Note that I also had to add a "Then" to your first If line since your code above didn't have it.

Sub Sort()
    Dim e As Range
    Dim foundVal As Range
    Dim LR As Long
    LR = Range("C" & Rows.Count).End(xlUp).Row
    
    For Each e In Range("S9:S16")
        Set foundVal = Range("D9:D62").Find(e, LookIn:=xlValues, lookat:=xlWhole)
        If foundVal Is Nothing And e.Offset(, 6) = "Out of Stock" Then
            Range("S" & e.Row & ":W" & e.Row).Copy Range("D" & LR + 1)
            LR = LR + 1
        ElseIf Not foundVal Is Nothing And e.Offset(, 6) = "Available" Then
            Range("D" & foundVal.Row).Offset(1).EntireRow.Insert
            Range("S" & e.Row & ":W" & e.Row).Copy foundVal.Offset(1)
            LR = LR + 1
        End If
    Next e
End Sub

 

[Joe4]

One note:

You should NEVER used reserved words (words that are used as named of existing function, properties, methods, or objects) as the names of your sub procedures, user defined functions, or variables. "Sort" is such a reserved word. Using it as the name of a procedure could cause errors and unexpected results, as when you try to call it, Excel may not be able to tell if you mean the built-in Sort methodology, or your procedure.

I often preface the names of my procedures with "My", i.e, "MySort". Then I never have to worry about that possibility.

 

[angel34]

Thank you shknbk2 for choosing to reply to my query. I shall test the code and revert back with the feedback. The Then got omitted while copying and pasting the code here. Thank you for pointing this out.

Thank you Joe4 for the suggestion. I stand corrected and changed the sub name to MySort.

 

[angel34]

The code worked perfectly. Thank you shknbk2. Code has been marked as the solution.