Home > other >  Optimize Performance of InStr check over range VBA
Optimize Performance of InStr check over range VBA

Time:07-09

Is it possible to speed up the below Sub() that checks for the - character in a range? It is looping over 25000 rows across 3 columns and doesn't perform as fast as I'd like it to.

Sub Character_Check()

For Each tCell In Range("A1:C25000")
    If InStr(tCell.Text, "-") > 0 Then
        MsgBox "True"
    End If
Next

End Sub

CodePudding user response:

Solution:
For these scenarios (massive same computation) I think it's faster to store the values in an array and then just set the results to the range at once. Refer to enter image description here

CodePudding user response:

Try the below simple alternative and see if it improves the performance:

Sub Find_All()
   Dim rangeFirst As Range, rangeCurrent As Range
   Dim searchRange As Range
   Set searchRange = Range("A1:C25000")
   Do
      If rangeFirst Is Nothing Then
         Set rangeFirst = searchRange.Find(What:="-")
         Set rangeCurrent = rangeFirst
      Else
         Set rangeCurrent = searchRange.Find(What:="-", After:=rangeCurrent)
         If rangeCurrent.Address = rangeFirst.Address Then Exit Do
      End If
         MsgBox (rangeCurrent)
   Loop
End Sub
  • Related