我找到了答案!绝对可以这样做,但不使用 .FindNext
作为解决方案。参考我在这里找到的关于 SO 的文章,我根据集体方法即兴提出了以下解决方案,它完全符合我的要求。
注意:StartTimer
和 StopTimer
使用一种方法来计算执行功能所需的时间,并为我提供精确到秒的输出(不完全准确,但足够准确) -- 你可以找到这个 here.
的函数
本节开始查找我的查询的第一次迭代的过程,然后隐藏数据之前的行(为了清晰可见):
'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
此部分执行 Do
循环而不使用任何 While
或 Until
,并停止 .Find
函数循环遍历我文件中剩余的数据单元格。
(解决方案)
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",_
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
现在 - 为了尽可能彻底和公平,我确实测试了我的问题中列出的 xlPrevious
方法,它确实为包含匹配项的最后一个单元格生成了相同的地址。
但是
xlPrevious
方法确实需要更长的时间来遍历剩余的 7900+++ 个单元格。在这种情况下,计时花费了大约 2 秒,我发现这仍然令人印象深刻,因此它相对较快,但是,如果数据范围扩大,随着时间的推移,这将花费更长的时间,或者,如果给定的范围不仅仅是一个对于此文件中的相同给定数据,单列将花费更长的时间(测试并发现大约需要 10 秒。匹配单元格地址的结果相同)。
唯一一次我看到解决方案无用的情况是,您可能有更多行,然后是另一块与以前相同的查询匹配的全等数据,但即使在这种情况下,您也可以放入一部分代码使搜索查询重新开始,但使用 after
参数指定搜索在最后找到的单元格之后开始。在我的数据中,永远不会出现在另一个与查询不匹配的一致数据块之后再次找到相同数据的情况。同样,为了公平起见,这里是使用 xlPrevious
比较的方法,如 final SO article I listed in the question.
中指定的
Call StartTimer
'topRow,_
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",SearchOrder:=xlByColumns,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
最后 - 一些结果
这是从我的测试中打印出来的即时窗格:
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
本文链接:https://www.f2er.com/4072.html