findnext working in 3 out of 4 situations - help me understand why it falls over

[Andyatwork]

Hello all,

I've hacked together some code that lets a user select multiple csv files, glue them into one big file and then strip out various rows depending on whether certain keywords appear in particular columns.

The keywords are noted on a spare tab of the workbook containing the code and all the data shuffling happens in a new book that is created when the csv files are selected and glued together.

The various iterations of the "find keyword in a given column and cut the row out to a different tab" all use pretty much identical code;

'   define table range of Class Name search terms in "tables" tab
    With tables
        tbllRow = .Cells(.Rows.Count, "a").End(xlUp).Row
    End With
    Set srcTbl = tables.Range("a5:a" & tbllRow)
    srcArr = srcTbl

'   Search Employment status column for keywords
    With shData.Range("j:j")
        rCount = 1  'first pass so first blank row is 2.
        For i = LBound(srcArr) To UBound(srcArr)
           srcStr = srcArr(i, 1)
           Application.StatusBar = "Purging status keyword...>> " & srcStr & " <<"
            Set fndRng = .Find(what:=srcStr, after:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, LookAt:=xlPart, _
                            SearchOrder:=xlByRows, searchdirection:=xlNext, _
                            MatchCase:=False)
                    
            If Not fndRng Is Nothing Then
                Do
                    rCount = rCount + 1
                    fndRng.EntireRow.Cut dead.Range("A" & rCount)
                    Set fndRng = .FindNext(fndRng)
                Loop While Not fndRng Is Nothing
            End If
        Next i
    End With

In the above snippet it searches through column J of the shData tab for keywords in my search array, when found, it cuts the row, boots it into (worksheet) dead and keeps going before picking up the next keyword from the array and looping through the column again.

This all works fine. The problem occurs when I need to modify the behaviour slightly. Instead of searching the entire column for banned keywords and booting out every instance of them, I need to search a limited selection of a given column for banned keywords. e.g., Keyword "good" is normally allowed except if it appears in a special subset of the data which is identified by another keyword in a different column.

When I test the code on the subset of data alone, it works. When I test the code on the full range of data it suddenly falls over.

The affected module is;

    Dim i As Long           '  variable for row number in search term table
    Dim strClass As String  '  search term in class name column; "keyword identifying special subset of data"
    Dim rngClass As Range   '   define range object for class name
    Dim rngCell As Range    '   define cell object in range
    Dim FirstCell As Range, LastCell As Range
    Dim firstaddress As String
    Dim srcRng As Range, fndRng As Range
    Dim srcArr() As Variant         'search terms array
    Dim srcTbl As Range             'search terms range on "Tables"
    Dim srcStr As String            'search term
    Dim lrow As Long, lcol As Long
    Dim kCount As Long, rCount As Long, tbllRow As Long
    
'   checks last row of data tab in output workbook to be searched
    With shData
        lrow = .Cells(.Rows.Count, "B").End(xlUp).Row
    End With
    Set rngClass = shData.Range("B1:B" & lrow)      '   define range of cells in class column
    
'   check last used row of kill tab
    With kill
        kCount = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
'   determine first and last cell in class column containing strClass
    strClass = "Keyword identifying special subset of data"
    Set FirstCell = rngClass.Find(strClass, , xlValues, , xlByRows, xlNext)
    Debug.Print "first cell is "; FirstCell.Address
    Set LastCell = rngClass.Find(strClass, , , , , xlPrevious)
    Debug.Print "last cell is "; LastCell.Address
    
'   define table range of Class Name search terms in "tables" tab
    With tables
        tbllRow = .Cells(.Rows.Count, "E").End(xlUp).Row
    End With
    Set srcTbl = tables.Range("E5:E" & tbllRow)             '   define range of cells from Table tab
    srcArr = srcTbl                                         '   set array of search terms
    
    Set srcRng = shData.Range("j" & FirstCell.Row & ":j" & LastCell.Row)
'   Search Leaver Reason column for keywords
    With srcRng
        For i = LBound(srcArr) To UBound(srcArr)    '   set number of cycles = to # of keywords
            srcStr = srcArr(i, 1)                   '   select keyword from array by reference to cycle #

            Set fndRng = .Find(what:=srcStr, after:=.Cells(.Cells.Count), LookIn:=xlValues, _
                            LookAt:=xlPart, SearchOrder:=xlByRows, _
                            searchdirection:=xlNext, MatchCase:=False)
                 
            If Not fndRng Is Nothing Then          '   end find when range ends/is nothing
                firstaddress = fndRng.Address
                Do
                    kCount = kCount + 1
                    fndRng.EntireRow.Cut kill.Range("A" & kCount)
                  [COLOR="#FF0000"][B]  Set fndRng = .Findnext(fndRng) [/COLOR][/B]
                Loop While Not fndRng Is Nothing ' And fndRng.Address <> firstaddress
            End If
        Next i                                      '   cycle to next keyword

    End With

End Sub

Specifically it falls over on the bold red text at the bottom of the second set of code. Findnext becomes = to nothing so fndrng becomes invalid and thunk.
I don't understand how it could be working fine when I run on just the special subset, but it stops working when in the full set of data.

I think it must be something to do with searchrange (srcRng) being a defined with firstcell.row and lastcell.row instead of being the entire column. When watching the variables as I step through the code, the find is not checking each row in turn but appears to be jumping all over the place and I don't understand why. Something to with the optional after:= .cells(.cells.count) maybe?

Any pointers?

Also, tips on making it faster as I am aware that Do Loops are the slow way of doing this but I am still a novice at VBA.

Many thanks,

Andy

 

[Andyatwork]

Addendum; I have discovered that if the special subset of data is the last in the overall set of data, it works. If it is the first, or in the middle, it falls over.

I feel this is important but the significance escapes me.