execution of code takes very long

[Ritesh S.]

This code is suppose to take the value of the row and
insert it above the current row and add the suffix at the
end.
IN THIS CASE... I am starting with list of 30 product and
there are 15 suffix...so it takes each product and insert
15 lines with different suffix from array. (30*15=450rows)

It seems simple to me... Sometimes it takes very long
time for execution...sometimes if i close the file and
open it again...it does it faster...but lately its not
even doing that...

Can you please suggest how i can make this work faster or
if i need clear the clipboard or variables...and how do i
do that...

Please help...

Thank you very much in advance...
-------------------------------------

Suffix_array = Array
("AA$", "AL$", "LA$", "LL$", "LU$", "IA$", "IL$", "IVV", "
AAS", "ALS", "LAS", "LLS", "LUS", "IAS", "ILS")

With Sheets(PROD_Sheet)

Cur_row = 2
Do While .Cells(Cur_row, 1) <> ""
Find_row = .Columns(1).Find(.Cells(Cur_row,
1), , xlValues, xlPart).Row
If Find_row = Cur_row Then
For Each suf In Suffix_array
With .Rows(Cur_row)
.Copy
.Insert (xlDown)
End With
.Cells(Cur_row, 1) = .Cells(Cur_row,
1) & ">" & suf
Cur_row = Cur_row + 1
Next
End If
.Rows(Cur_row).Delete (xlUp)
Loop

--------------------------------------------

 

[Tom Ogilvy]

Dim Suffix_Array as Variant
Dim num as Long
Dim lastow as Long
Dim rng as Range, sVal as String
Dim k as long, j as long, i as long
Suffix_array = Array( "AA$", "AL$", "LA$", "LL$",_
"LU$", "IA$", "IL$", "IVV", " AAS", "ALS", "LAS", _
"LLS", "LUS", "IAS", "ILS")
Application.Calculation = xlManual
num = ubound(Suffix_array) - lbound(suffix_array) + 1
set lastrow = cells(rows.count,1).End(xlup).row
for i = lastrow to 2 step -1
set rng = cells(i,1).offset(1,0).Resize(num-1,1).Entirerow
rng.Insert
cells(i,1).Entirerow.copy destination:=rng
sVal = cells(i,1).value
k = 0
for j = lbound(suffix_array) to ubound(suffix_array)
cells(i,1)(k).Value = sVal & ">" & suffix_array(j)
next
Next
Application.Calculation = xlAutomatic

Untested but should be close. Test it on a copy of your data.