Improve Speed: VBA Code very slow due to changing cell values in for loop

You have

Please Login or Register  to view this content.

So for the first Loop that results in

Please Login or Register  to view this content.

On the next Loop, the result is

Please Login or Register  to view this content.

On the Loop after that

Please Login or Register  to view this content.

On the last Loop you'll get

Please Login or Register  to view this content.

Is that what you want?
Or do you mean it to be

Please Login or Register  to view this content.

If you go out of your way and code it as inefficient as you can (many loops), it still is fast enough.

Please Login or Register  to view this content.

Num_Unique_Codes should be

Please Login or Register  to view this content.

instead of the code you have now.
The same for Numrows

Please Login or Register  to view this content.

Originally Posted by Nick_G

. . . I have the following piece of VBA code: . . .
:
Range("K5") = Application.Sum(Range("C20:C" & Numrows).SpecialCells(xlCellTypeVisible))
:
Range("A20:L" & Numrows).SpecialCells(xlCellTypeVisible).Clear
:
This works 100% perfectly from a functionality point of view, but is very slow. . . .

The most likely way to speed up VBA macros which change cell contents, e.g., assigning values to cells, or clearing their contents, is to begin the macro with

Dim acm As Variant
acm = Application.Calculation
Application.Calculation = xlCalculationManual

and end it with

Application.Calculation = acm

ADDED: Also consider disabling event handlers at the beginning and reenabling them at the end.

Originally Posted by jolivanes

Or do you mean it to be

Please Login or Register  to view this content.

Doh! Thank you - I'm kicking myself for not picking that up!

Originally Posted by jolivanes

Num_Unique_Codes should be

Please Login or Register  to view this content.

instead of the code you have now.
The same for Numrows

Please Login or Register  to view this content.

Thank you.