PDA

View Full Version : Code VBA Excel slowly



niwat2811
08-21-2012, 07:24 PM
Sub ProcessData()
Range("B2").Select
ActiveCell.FormulaR1C1 = "=SUMIFS(Database!C[10],Database!C3,Result!RC1)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B14"), Type:=xlFillDefault
Range("B2:B14").Select
Selection.Resize(13, 280).FillRight
Range("A1").Activate
End Sub
code vba very slow in the information sheet that will be about 100,000 rows.
Can be improved to run faster than me.

fredlo2010
08-21-2012, 11:52 PM
Try this and see if this is better.

Sub ProcessData()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("B2").Resize(13, 280).FormulaR1C1 = "=SUMIFS(Database!C[10],Database!C3,Result!RC1)"
Range("A1").Activate

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

snb
08-22-2012, 12:32 AM
Avoid any 'Select' or 'Activate' in VBA