I have the following macro and have tried a few things to make it run quicker but it still takes too long.
the below code only runs on one column but i have 40 such columns. so all in all macro takes about 30 mins to run.
Any idea how i can make it faster or other ways of achieving the same result.
Sub Sumproduct() Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlManual Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("B3").Resize(LastRow - 1).Formula = "=SUMPRODUCT((S6OBData!A:A=A3)*(S6OBData!B:B=B$2),S6OBData!C:C)" Range("B3:B2000").Value = Range("B3:B2000").Value End With Application.EnableEvents = True Application.ScreenUpdating = True Application.Calculation = xlAutomatic End Sub
many thanks for help,