Hello,

I have the following VBA subroutine that sums up cost for all rows
with an item type = ZSTP or ZRMT that have the same guid (guid in col
E) and puts the total onto a separate (but existing) row with an item
type ZRPR and the same guid. I am a novice with VBA so I am writing an
array formula in column AD and then doing a copy paste values into the
original column with cost, i.e. V. I then delete ZSTP rows. The code
below is not efficient at all with 1000's of lines and I would
appreciate pointers for optimizing it or revisiting this approach.

Thanks,

Manish

Sub COST_TOTAL()

Dim lastrow As Integer
Dim i As Integer

Application.ScreenUpdating = False
Range("AD27").FormulaArray =
"=SUM(IF(R27C2:R5000C2=RC[-28],IF(OR(R27C4:R5000C4=""ZRMT"",R27C4:R5000C4=""ZSTP ""),IF(RC[-28]=RC[-25],R27C22:R5000C22,0),
0),0))"
Range("AD27").Copy Range("AD28", Range("AB65536").End(xlUp).Offset(0,
2))
Application.CalculateFull
Range("AD27").Select
Range("AD27", Selection.End(xlDown)).Select
Selection.Copy
Range("V27").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Worksheets("Sheet1").Columns("V"). _
NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
Columns("AD").EntireColumn.Delete
Columns("B").Hidden = True
Columns("E").Hidden = True
lastrow = [A65536].End(xlUp).Row
For i = lastrow To 1 Step -1
If Cells(i, 4) = "ZSTP" Then Rows(i & ":" & i).EntireRow.Delete
Next i
Application.ScreenUpdating = True
End Sub