Slow VBA code?

[Gijs Breedveld]

I have code which clears a table which was generated the previous run. The
code is:

Range("b2000020000").Select
Selection.AutoFill Destination:=Range("b1020000"), Type:=xlFillDefault
Range("b1020000").Select

I just want to clear the contents and the formats of the previous table
before I build up the next table.

Is there code which can do this faster? It is very slow. Takes about 60
seconds.

Best regards, Gijs

 

[Gary Keramidas]

try either of these:

Sub test()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With Range("b2000020000")
.Clear
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub

Sub test2()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Range("b2000020000").AutoFill Destination:=Range("b1020000"), _
Type:=xlFillDefault
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub

 

[Bob Phillips]

You can get rid of the select

Range("b2000020000").AutoFill Destination:=Range("b1020000"),
Type:=xlFillDefault
Range("b1020000").Select

but I doubt that will make much difference. Also try turning screenupdating
off and setting calculation to manual, and reset after.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gijs Breedveld]

Bob,

Thanks .

Best regards,

Gijs

You can get rid of the select

Range("b2000020000").AutoFill Destination:=Range("b1020000"),
Type:=xlFillDefault
Range("b1020000").Select

but I doubt that will make much difference. Also try turning screenupdating
off and setting calculation to manual, and reset after.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gijs Breedveld]

Gary,

Thanks, this speeds things up.

Best regards,

Gijs