Trying to improve the performance of my code

[Carlo]

Hi All

with following code i'm splitting up text into rows.
The textpassages aren't longer than ColWidth.
'-------------------------------------------------------
Sub Tool_Hyphenation(TextSplit As String, EndRange As Range, ColWidth As
Double, Collength as Byte)

Dim tmpstr, ApprovedStr, StrScore As String
Dim Arrstr, ArrTmpStr, ArrScore As Variant
Dim Fontsize_ As Single
Dim Linenr, LenArrStr, ScoreCount As Integer

Fontsize_ = 7.5
Arrstr = Split(TextSplit)

Columns("IV").Clear
Columns("IV").Font.Size = Fontsize_
Columns("IV").Font.Name = "ITCFranklinGothic LT Book"
Linenr = 1
tmpstr = ""

For i = 0 To UBound(Arrstr)

tmpstr = Trim(tmpstr & " " & Arrstr(i))
If Len(tmpstr) > ColLength Then
ActiveSheet.Range("IV" & Linenr) = tmpstr
Columns("IV:IV").AutoFit
If Columns("IV:IV").ColumnWidth <= ColWidth Then
ApprovedStr = tmpstr
Else
ActiveSheet.Range("IV" & Linenr) = ApprovedStr
Linenr = Linenr + 1
tmpstr = Arrstr(i)
End If
End If
Next i

If Range("IV" & Linenr).FormulaR1C1 <> tmpstr Then
Range("IV" & Linenr).FormulaR1C1 = tmpstr
End If

Range("IV1:IV" & Linenr).copy Range(EndRange)
Columns("IV:IV").Clear

End Sub
'-------------------------------------------------------

The problem now is, that this code, like it is now, is really slow.
Does anyone has an idea how the performance can be improved?
I improved it a little with the use of Collength, which controls the
length of tmpstring.

I appreciate every comment.

Thanks in advance

Carlo

 

[Gary Keramidas]

try adding these 2 lines at the beginning of your code:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

and add these before the end sub:

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

 

[Carlo]

Hi Gary

thank you for your input!

instead of 40 seconds it ran for 36 seconds. Which is still long!
But it is an improvement!

I hope someone can give me a hint with which i am able to
go down to a duration of maximum 20 seconds or so!
Would really appreciate it.

Thanks again

Carlo