VBA Code Optimization for Array Formulas
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 |
All times are GMT +1. The time now is 08:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com