I have been struggling to get a Do/While loop using Range.Find and Range.FindNext methods to break out of an infinite loop. I have researched all of this and found the tons of duplicate questions asking generically "how do I stop the infinite loop?" but these do not answer the specific condition I am looking for since I do not want to simply stop the Do loop when the .find function finds the last iteration and returns to the first iteration found of the matching text (i.e. using Loop While Not testFind is Nothing And nxtAddr <> firstAddr type construct).
let's look at some data examples. I have roughly 8000+++ rows of data on any given file I'm working on. In the file, I'm searching for dates/times, specifying a date and specific hour (for example - "20210715 12" as my query):
Row 37: 20210715 14:07:21 ---- This row is the last row of a previous set of congruent data. (Row 37 and all other data above minus header rows are hidden for user's viewing pleasure.) Row 38: 20210715 12:48:20 ---- This row is the first row of the congruent data - I CARE ABOUT THIS DATA Row 39: 20210715 12:47:20 Row 40~85: Date stays same, hour stays the same, rest of time increments down Row 86: 20210715 12:07:15 Row 87: 20210715 12:07:13 ---- This row is the final row of the congruent data - I CARE ABOUT THIS DATA Row 88: 20210715 11:54:20 ---- This row is the first row of a new set of congruent data.
A typical Do/While loop for this can be found in multiple places, including duplicates right here on SO. These examples (here, here, or here for example) do NOT prevent .find or .findnext methods from looping through the entire rest of my range in order to assure there are "no more matches."
Referencing THIS SO article, and THIS SO article, I stumbled upon a hypothesis of whether the range could be dynamically shifted through the congruent data without Range.Resize (which does not just shift the range down the rows - it increases the size of the current range being tested). . .
Is this possible, and does it work? I did happen to run into a very eye-opening facepalm moment, when I stumbled upon this incredibly simple and eloquent SO solution of returning the final row matching the same text by changing the SearchDirection=xlNext to SearchDirection=xlPrevious and thought "EUREKA!"
This solution of using xlPrevious in the Range.find method STILL FORCES A LOOP THROUGH ALL OF THE REMAINING ROWS BELOW THE ENTRY UNTIL IT FINDS THE MATCH!!!
Bottom Line: Can the loop be broken once it searches through a section of congruent data as shown above, ending on the final Row, without having to iterate through the remaining 7900+++ rows of data?
For those who may ask - I specifically also do NOT want to use a For i = rows.count To 1 Step -1 construct answer either, as again, I am NOT wanting to loop through all of the cells to find the last iteration!!!
I have found the answer! It most definitely IS POSSIBLE to do this, but does NOT use .FindNext as the solution. Referencing the articles I found here on SO, I improvised on the collective methods came up with the following solution which does exactly as I want it to.
Note: the StartTimer and StopTimer uses a method to calculate the time it takes to perform a function and gives me the output down to the second (not completely accurate, but accurate enough) -- you can find functions for this here.
This section starts the process of finding the first iteration of my query and then hides the rows before the data (for clean visibility's sake):
'Note - the values of topRow, dateCol, and r were determined earlier in code, r = rows of used data (NOT USING "USEDRANGE").
With Range(Cells(topRow, dateCol), Cells(r, dateCol))
Set test = .Find("20210715 12", LookIn:=xlValues, Lookat:=xlPart, _
searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False)
If Not test Is Nothing Then
'sets the address of cell above first found match to _not_ hide the first matching cell
strtFndCel = test.Offset(-1, 0).Address
'sets the first found cell's address
strtFnd = test.Address
'sets the next cell's address to specify for future search range.
strtFndNxt = test.Offset(1, 0).Address
Debug.Print "The First Cell Address containing my query = " & strtFnd
Set strtRng = Range(Cells(topRow, 1), strtFndCel)
strtRng.Rows.Hidden = True
End If
End With
And this section performs the Do loop without any use of While or Until and stops the .Find function from looping through the remaining cells of data in my file.
Dim findrng As Range
'Uses the strtFndNxt value to set the defined range for the next query of congruent data
Set findrng = Range(strtFnd, strtFndNxt)
Call StartTimer
Do
Set test = Nothing
Set test = findrng.Find("20210715 12", LookIn:=xlValues, Lookat:=xlPart, _
searchdirection:=xlNext, MatchCase:=False)
If test Is Nothing Then
Exit Do
Else
'Note - when looping, the "lastFnd" address increments by 2, but will adequately stop
'on the appropriate cell regardless of even or odd number of rows.
lastFnd = test.Address
strtFnd = test.Offset(1, 0).Address
strtFndNxt = test.Offset(2, 0).Address
Debug.Print "The Current Address containing my query = " & lastFnd
Set findrng = Range(strtFnd, strtFndNxt)
End If
Loop
Debug.Print "The Last Address containing my query using breaking loop Method = " & lastFnd
Call StopTimer
Now - in order to be as thorough and fair as I possibly could, I did test the xlPrevious method as listed in my question, and it does produce the same address for the final cell containing the match.
The xlPrevious method did take longer to iterate through the remaining 7900+++ cells. In this case the timing took about 2 seconds which I found to still be impressive, so it is relatively quick, however, if the range of data expands, this will take longer over time, or, if the range given is more than just a single column it would take even longer with the same given data in this file (tested and found to be roughly 10 seconds. . . same result of the matching cell address).
The only time I could see the Solution being unhelpful is in situations where you may have more rows and then another block of congruent data matching the same query as before, but even in this case, you could put in a portion of code to make the search query start again, but using the after parameter to specify the search begins after the last found cell. In my data, there will never be a situation where the same data will be found again after another block of congruent data which does not match the query. Again, for fairness, here is the method using the xlPrevious by comparison as specified in final SO article I listed in the question.
Call StartTimer
'topRow, dateCol, and r were determined earlier in code, r = rows of used data (NOT USING "USEDRANGE").
With Range(Cells(topRow, dateCol), Cells(r, dateCol))
Set test = .Find("20210715 12", LookIn:=xlValues, Lookat:=xlPart, _
searchdirection:=xlPrevious, MatchCase:=False)
If Not test Is Nothing Then
lstFnd = test.Address
End If
End With
Debug.Print "The Last Address containing my query using xlPrevious Method = " & lstFnd & vbCrLf & "Range Searched is on single column only."
Call StopTimer
'Uses the entire working range to perform the same query
'instead of on just a single column, but searches by columns
'to make the efforts a little more expedient.
Call StartTimer
'workrng was specified in previous code - NOT USING "USEDRANGE" methods.
With workrng
'Note: removing SearchOrder to use default behavior made NO DIFFERENCE in Timing!
Set test = .Find("20210715 12", LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByColumns, _
searchdirection:=xlPrevious, MatchCase:=False)
If Not test Is Nothing Then
lstFnd = test.Address
' Debug.Print strtFnd
' Debug.Print strtFndNxt
End If
End With
Debug.Print "The Last Address containing my query using xlPrevious Method = " & lstFnd & vbCrLf & "Range Searched is entire working range."
Call StopTimer
Here is what printed off of my Immediate Pane from the testing:
The First Cell Address containing my query = $A$38 The Starting Time is: 14:51:33 The Current Address containing my query = $A$39 The Current Address containing my query = $A$41 . . . The Current Address containing my query = $A$47 The Current Address containing my query = $A$49 . . . The Current Address containing my query = $A$85 The Current Address containing my query = $A$87 The Last Address containing my query using breaking loop Method = $A$87 The End Time is: 14:51:33 The time it took to run this process is: 00:00:00 The Starting Time is: 14:51:33 The Last Address containing my query using xlPrevious Method = $A$87 Range Searched is on single column only. The End Time is: 14:51:35 The time it took to run this process is: 00:00:02 The Starting Time is: 14:51:35 The Last Address containing my query using xlPrevious Method = $A$87 Range Searched is entire working range. The End Time is: 14:51:45 The time it took to run this process is: 00:00:10