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

 

[CautionMP]

Disable calculations? I'm can never remember if [tt]ScreenUpdating[/tt] and [tt]EnableCalculation[/tt] do the same thing but it would be worth a try.

Just remember to enable calculations at the end of your routine.

Hope this helps,
CMP

 

[mbarnett]

Hi,

Thanks for the response. I tried that. Still very slow

 

[CautionMP]

[tt]Application.Calculation = xlCalculateManual[/tt]?

 

[mbarnett]

Hi,

Yes...It still runs very slow.....It is driving me crazy...It is a simple sumif vba code.....there has to be a faster way

 

[CautionMP]

I'm an idiot! [tt]Application.WorksheetFunction[/tt] is going to perform the calculation regardless of the Calculation setting.

It might go quicker to actually write the equation [tt]Cells(i, 28).Formula=...[/tt](with calculation disabled), then enable calculation. If you are hidding the formula, select the column, copy, and paste special->values.

I know it goes against human nature but jumping through hoops is sometimes faster.

CMP

 

[mbarnett]

Hi,

I tried your suggestion. It ran a little bit faster. It took 20 minutes instead of 25. I appreciate your help...I thought the formula in VBA would go very quickly

 

[AnotherHiggins]

It is always faster to avoid looping if possible. Try your code with the following changes:

Sub BBGDownLoad12()
Dim lastcell As Long

lastcell = Cells(65000, 3).End(xlUp).Row
Sheets("Download").Select
Range("AB2", "AB" & lastcell) = "=SUMIF(fundingtradedata,RC[-27], fundingpl)"
Application.ScreenUpdating = True
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Don't use the Select Method.


How Can I Make My Code Run Faster? faq707-4105


Skip,
[sub]
[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway! [/sub]

 

[CautionMP]

...and there you go. Always get your FAQ's straight, first.

Thank you Skip for this important reminder to check the FAQ's, and more importantly, for authoring them.

CMP

 

[mbarnett]

Hi Guys,

Thanks for you replies. i really appreciate all the help..I changed my code to what anotherhiggens suggested. I'm currently working with 28,000 rows and the macro crashes my spreadsheet (Not Responding). Iam using application>screenUpdating=false and application.Calculation=xlManual, but still no luck. If you have anyother suggestion, I would greatly appreciate the help.

Thanks
Mike

 

[GlennUK]

Fill 1000 cells at a time instead of doing 28000 in one go. Filling 28000 cells with a SUMIF calculation is asking too much.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[mbarnett]

Hi GlennUK,

I will give it a try. Thanks for your suggestion.

 

[mbarnett]

Hi GlennUK,

How do I get it to do the remaining 27,000 rows. Not sure how to program that in the below code.

Sub BBGDownLoad12()
Dim lastcell As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
lastcell = 1000
Sheets("Download").Select
Range("AB2", "AB" & lastcell) = "=SUMIF(fundingtradedata,RC[-27], fundingpl)"
Application.ScreenUpdating = True
End Sub

 

[GlennUK]

Try this:

Sub BBGDownLoad12()
Dim lastcell As Long

lastcell = Cells(65000, 3).End(xlUp).Row
Sheets("Download").Select
loopend = Application.WorksheetFunction.Ceiling(lastcell, 2000)
copystart = 2
For iloop = 2000 To loopend Step 2000
    copyend = Application.Min(iloop, lastcell)
    Range("AB" & copystart, "AB" & copyend) = "=SUMIF(fundingtradedata,RC[-27], fundingpl)"
    copystart = iloop + 1
Next
Application.ScreenUpdating = True
End Sub

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[mbarnett]

Hi,

I appreciate the code GlennUK. I works great for less than 10,000 rows, but the s/s freezes up if the data is greater than 10,000 rows. Not sure how to proceed. It is a little frustrating process.....

 

[Zathras]

I'm having a little trouble understanding how all of these suggestions can be made without knowing what is the definition of "fundingtradedata" and "fundingpl" along with not knowing what kinds of entries may be found in column "A"

Some sample data would be nice.

I can't quite see the big picture. SUMIF implies that the value in column "A" can appear multiple times in the "fundingtradedata" range. But if that's the case then there should be many values in colum "A" that don't appear at all. Perhaps some pre-process with VLOOKUP to determine whether the SUMIF is required could help. (It might also just make the process slower. Again, it would be nice to know something about the shape of the data.)

If I were to shoot from the hip too, I could suggest that a Pivot Table might be useful for this scenario. Also, depending on what the data actually look like, a Data Table may be the way to go.

 

[mbarnett]

Hi,

Apologies for not being clear. "fundingtradedata" and "fundingpl" are ranges that contain info needed in my download tab. There will always be data in these ranges for my tab. Pivot Table would not work because i'm using the data i'm pulling in from the sumif to calculate other values in the download tab. I hope this is clear. i can send a sample s/s if you would like.

Again I appreciate any help.

 

[Zathras]

I wasn't suggesting that a Pivot Table would provide the final result you want. But it could do the summarizing for you and then you can pick off the summed values with VLOOKUP formulas. Should be faster than SUMIF. (If you think about it, SUMIF has to scan all the rows every time. VLOOKUP only has to scan half of the rows each time, on average.)

There's no way to send a s/s (we don't do that here), but if you could strip the process to the bare bones and supply some sample data of a dozen or so rows to illustrate what you are doing we might be able to help you a little better.

Since you are using VBA, the entire process is open to revision. The SUMIF may or may not be the best tool for the job. There's no way to know with what you've posted so far.

 

[Gavona]

Are you filling this column with formulae still?
If so consider doing in chunks as suggested above but calculate and copy to values each chunk before moving on to the next. Something like:

With Myrange
.formula = "......
.calculate
.copy
.pastespecial....
end with

Gavin