which option faster and why?

I have a column of names which i am trying to parse.

1) The first ways is to go through each cell in the column and parse it accordingly. Here is the code i used to parse the col of strings.

For RowIndex = 2 To last_row1
        If InStr(myinputwrksht.Range(alphacol(Hdr_ProdNme_col) & RowIndex).Value, "-") > 0 Then
            ProductName = myinputwrksht.Range(alphacol(Hdr_ProdNme_col) & RowIndex).Value
            myinputwrksht.Range(alphacol(Hdr_ProdNme_col) & RowIndex).Value = Trim(Left(ProductName, InStr(1, ProductName, "-", 1) - 1))
        End If
Next

2) the 2nd way is to insert a col next to the column of interest. insert "excel formulas" into this new col which modify the previous col entries. Next fill down formulas, cut-n-paste values into previous col, then del col you added.





Which way is faster? The 2nd way................go figure.......would never have guessed it.

I have a macro file that is about 1.3Meg in size. When i run this macro and try to run option 1 it takes about 10 sec as compared to about 1.8 secs with the 2nd option.


note: If i just want to parse the col of strings and I DONT have this 1.3Meg file open......ie...just parse file using a test file.........it takes no time at all for either option to run. But for some reason option 2 is fastest when i have this 1.3meg macro file open.

any thoughts?