Hi,
I'm using a macro for searching through ~200k rows of a column, finding all the occurrences of a string and copying them to another column.
This is the code (copied from somewhere some time ago, modified as needed) :
Sub Copy()
Dim strArray As Variant
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim NoRows As Long
Dim DestNoRows As Long
Dim i As Long
Dim J As Integer
Dim rngCells As Range
Dim rngFind As Range
Dim Found As Boolean
    strArray = Array("*h1*")
    Set wsSource = Sheets(3)
    NoRows = wsSource.Range("A500000").End(xlUp).Row
    DestNoRows = Sheets(2).Range("CA1").End(xlUp).Row
    Set wsDest = Sheets(2)
    For i = 1 To NoRows
    Set rngCells = wsSource.Range("A" & i & ":A" & i)
    Found = False
    For J = 0 To UBound(strArray)
    Found = Found Or Not (rngCells.Find(strArray(J)) Is Nothing)
    Next J
    If Found Then
    rngCells.Copy wsDest.Range("CA" & DestNoRows)
    DestNoRows = DestNoRows + 1
    End If
    Next i
End Sub
It works great but it takes a little less than 20 seconds to complete the task.
And, since I have to search for multiple strings and the results need to be copied to different rows, I use multiple subs like this in a bigger macro.
The problem is that it got to the point where it takes 3 minutes to execute that bigger macro and I'm trying to find a way to speed things up.
What can I possibly do?