Improve Do Loop?? Slow when data is greater than 2000 Rows??? 1

[mbarnett]

I have a great deal of data that I pull in daily. It can range from 200 rows to 35000 rows. The below code works great if the date is not greater than 2000 rows. Can anyone suggest how to improve the efficiency of the code to handle large amounts of rows??

Also, It needs to be done in excel. Please help!!!


Sub BBGDownLoad12()
Dim lastcell As Long

Application.ScreenUpdating = False
lastcell = Cells(65000, 3).End(xlUp).Row + 1
i = 2
Sheets("Download").Select
Range("AB2").Select
Do
If IsEmpty(lastcell) Then Exit Do
Cells(i, 28).Value = Application.WorksheetFunction.SumIf(Range("fundingtradedata"), ActiveCell.Offset(0, -27).Value, Range("fundingpl"))
i = i + 1
Loop

Application.ScreenUpdating = True
End Sub

 

[xlbo]

28,000 sumifs - sorry but it's gonna take a long while to calculate whatever you do. I would be inclined to look into a slightly different way of doing this. As Zathras has alluded to, a pivot table would take care of making sure you have discreet vlues - from there, you would not need the sumif formula (which is one of the less efficient formulae you can use anyway) so you may be able to proceed more quickly. Sometimes, doing things in multiple stages can actually be quicker than trying to do it all in one big lump...