VBA More effective way of data trimming
So I had a look at your code and I got confused to what you were trying to do with find. So I had to test it out for myself. If I understand it correctly you are using it to figure out if a string contains the value you are looking for. If it does you continue processing if not you skip it. 

However, this can be done by fetching the cell value and performing InStr &lt;&gt; 0 to do the same thing.  

I wrote a quick VBA to see the performance of find in relation to InStr. The results of this was (as you can test yourself now) that the .Find method took aproximately 5 seconds to compute, whereas the InStr one finishes instantly. When running on 100000 lines it takes almost a minute to complete, whereas the InStr one finishes instantly. So since you perform the Cells(x,y).Find for 4 separate columns in thousand of files I would say that this is your main culprit for timeloss. 

Sheet1.Cells(1, 2).Value = TimeValue(Now)

For i = 0 To 10000
Dim x
Set x = Sheet1.Cells(1, 1).Find(What:="12", LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If Not x Is Nothing Then
        Sheet1.Cells(1, 4) = "Yup"
    End If
Next i
Sheet1.Cells(1, 3).Value = TimeValue(Now)
Sheet1.Cells(2, 2).Value = TimeValue(Now)
For i = 0 To 10000
Set x = Sheet1.Cells(2, 1)
    If InStr(x, "12") &lt;&gt; 0 Then
        Sheet1.Cells(2, 4) = "Yup"
    End If
Next i
Sheet1.Cells(2, 3).Value = TimeValue(Now)


On top of that you are running Cells(x,y).Replace which is about twice as slow as just setting the value of the Replace as seen below. 

wks.Cells(BlankRow, 1) = Replace(wks.Cells(BlankRow, 1),"string to remove","")


The methods you are running for Find and Replace are supposed to be executed on Ranges for the maximum efficiency. Say if you instead running replace on each cell one by one you execute it on the entire column straight away like this: 

wks.Columns(1).Replace What:="testtest         : ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False


Then you will notice that the Replace is performed in less than a second for all rows. Making it a lot faster if you have massive files.