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

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

--


Gary


"Carlo" wrote in message
...
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

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

"Gary Keramidas" wrote:

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

--


Gary


"Carlo" wrote in message
...
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