Macro To Get Minimum Row Number Where Criteria is Met

[vcs1161]

I have this piece in my macro the identifies duplicate data based on the value in column B. It then deletes rows if the date value in column D where that date is the oldest date(s) and only keeps the most current one. Prior to this code I do sort the worksheet in ascending order (column B) then descending (column D).

With ActiveSheet
    lrow = .Range("B" & Rows.Count).End(xlUp).Row
    For i = lrow To 2 Step -1
        If .Range("B" & i).Value = .Range("B" & i - 1).Value And .Range("D" & i).Value < .Range("D" & i - 1).Value Then
          .Rows(i & ":" & i).Delete
                lrow = lrow - 1 'this part will stop when it reaches the minimum row number variable 
         End If
       Next i
End With

This worksheet can grow to be thousands of rows. So I would like to revise this so it only needs to go through the rows that fall between the minimum row number where the criteria is met and the lrow which is already determined. How do I get the row number I want to stop the row deletion process as it works it's way up row by row? So let's say the first duplication of the value in column B was found in row number 500 and I want to stop with the deletion process when it reaches that minimum row number.

Any help or insight is greatly appreciated.

[PCI]

Can you send a short sample to illustrate your data

[stnkynts]

There are a lot of ways you can do this, I guess it just depends on how you want to do it. One way would be for you to hard code each time the max and the min row. (like this)

With ActiveSheet
    lrow = 500 'or whatever you want
    minrow = 2 'or whatever you want
    For i = lrow To minrow Step -1
        If .Range("B" & i).Value = .Range("B" & i - 1).Value And .Range("D" & i).Value < .Range("D" & i - 1).Value Then
          .Rows(i & ":" & i).Delete
                lrow = lrow - 1 'this part will stop when it reaches the minimum row number variable 
         End If
       Next i
End With

Another way would be to prompt yourself with 2 input boxes prior to the code running (something like this)

With ActiveSheet
    lrow = Application.InputBox("Enter the max row")
    minrow = Application.InputBox("Enter the min row")
    For i = lrow To minrow Step -1
        If .Range("B" & i).Value = .Range("B" & i - 1).Value And .Range("D" & i).Value < .Range("D" & i - 1).Value Then
          .Rows(i & ":" & i).Delete
                lrow = lrow - 1 'this part will stop when it reaches the minimum row number variable 
         End If
       Next i
End With

*Note: I left out a lot of error checking here

You could also build a userform and just collect the info once each time. Many more ways to do it, i figured i would give you these ones.