Find where repeating values stop in a table

[jondavis1987]

I have a table that is thousands of rows long. There's seven repeating values in a column with the header Sieve. These values repeat in the same order after each other. Through a pivot table linked to this table i know there are two instances where insted of going No. 40 to No. 200 it goes from No. 40 to 1 1/2". Is there a quick way to find this without going through the thousands and thousands of rows looking for this?

Sieve

1 1/2"

3/4"

3/8"

No. 4

No. 10

No. 40

No. 200

 

[JLGWhiz]

See if this will fix your issue. It assumes the data is in column A and that is the only column of data.

Sub t()
Dim fn As Range, adr As String
Set fn = Range("A2", Cells(Rows.Count, 1).End(xlUp)).Find("No. 40", , xlValues, xlWhole)
    If Not fn Is Nothing Then
        adr = fn.Address
        Do
            If fn.Offset(1) <> "No. 200" Then
                Rows(fn.Offset(1).Row).Insert
                fn.Offset(1) = "No. 200"
            End If
            Set fn = Range("A2", Cells(Rows.Count, 1).End(xlUp)).FindNext(fn)
        Loop While fn.Address <> adr
    End If
End Sub

 

[jondavis1987]

That works perfectly! My sanity thanks you!

 

[JLGWhiz]

That works perfectly! My sanity thanks you!

You're welcome,
regards, JLG