(Solved) – excel vba find start and end row in column for multiple variables


In excel, I have a helper column (col O) that has a formula in each cell, the formula lets me know when the data changes from 1 to 2 to 3 to 4 etc. For this data set rows 2-7 are “1”, 8-16 are “2”, 16-22 are “3”. Using some formulas on the sheet I plan on looping thru the ranges, i =1-3 in this example. lrow is the last row of data on the sheet (lrow = 22). Range("P1") is the max # in the helper column.

In the code below StartRow works just fine, the EndRow is what’s not working. When i=1 StartRow should be 2, and EndRow should be 7, but it keeps returning 22 or sometimes 21. I’m not sure how to get EndRow= to 7, then 16, then 22. Any suggestions on how to fix this issue?

   For i = Range("o2").Value To Range("P1").Value

    '----------loop check for multiple BOMs

    Dim StartRow As Long, EndRow As Long
    With Sheets("BOM Load")

        StartRow = .Range("o1:O" & lrow).Find(what:=i, after:=.Range("o1")).Row
'   EndRow = .Range("o2:O" & lrow).Find(what:=i,after:=.Range("o2"),searchdirection:=xlPrevious).Row
    EndRow = .Range("o1:O" & lrow).Find(what:=i, searchdirection:=xlPrevious).Row

    End With

Leave a Reply

Your email address will not be published. Required fields are marked *