Optimize Performance of InStr check over range VBA

Question

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

I think you would have to use `countif(a1:c25000,"*-*")` if you want to find cells with - anywhere in the text. — JohnRC, Jul 08 '22 at 15:43
Alternative: Read the whole range into an 2-dimensional array and loop over that. — FunThomas, Jul 08 '22 at 15:45
Thank you! @JohnRC this is exactly what I was looking for. Is there a way to pass in multiple but separate characters? Looking to check for `-` and `{`. Also thank you @findwindow, I should have been more clear that I am looking for the character in the cells, not just the standalone `-`. — Finntech, Jul 08 '22 at 15:46
Depends what your purpose is. If you just want to know if there are any cells that contain either then use `countif(...) + countif(...)` with different chars in each. Cells that have both will be included twice. — JohnRC, Jul 08 '22 at 15:49
@JohnRC Do `"` work with this method? Seems to throw an error when I try. — Finntech, Jul 08 '22 at 16:05
@JohnRC `WorksheetFunction.CountIf(Range("A1:C30000"), "*" & Chr(34) & "*")` seems to work for handling the `"` character. — Finntech, Jul 08 '22 at 16:25
If you need to use the quote " character in a literal string you can use two consecutive quotes to denote a single quote. So `Debug.Print "This is the quote "" character"` will output the string _This is the quote " character_ to the debug window. — JohnRC, Jul 20 '22 at 13:40

score 1 · Accepted Answer · answered Jul 08 '22 at 15:57

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 this answer for a further explanation, by using it, we can see a substantial optimization.

Code

Sub Character_Check_2()
Dim DateTimeReportsRequest As Date: DateTimeReportsRequest = Now()
Dim arrMark As Variant
Dim CounterRow As Long
Dim CounterCol As Long
ReDim arrMark(1 To Cells.SpecialCells(xlCellTypeLastCell).Row, 1 To 1)
For CounterCol = 1 To 3 'A to C
For CounterRow = 2 To Cells.SpecialCells(xlCellTypeLastCell).Row 'Rows 2 to 25k
If InStr(Cells(CounterRow, CounterCol).Text, "-") > 0 Then arrMark(CounterRow, 1) = "TRUE"
Next CounterRow
Next CounterCol
    With Cells(1, 5).Resize(UBound(arrMark), 1)
    .Value = arrMark
    End With
MsgBox "Time to finish: " & DateDiff("s", DateTimeReportsRequest, Now()) & " seconds", vbOKOnly
End Sub

Demo
Testing for the 25k in 3 columns (assuming it needs to write it to the sheet which it's very demanding), we go from 11secs to ~1

score 1 · Answer 2 · answered Jul 08 '22 at 16:02

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

Optimize Performance of InStr check over range VBA

2 Answers2