Optimize Performance of InStr check over range VBA-CodePudding

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

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