Helps to make code faster

How can we test it? All you did was supply us with the code.

A couple of items I see here:

1) The exception is to use .Select not the norm. Therefore cut down lines like:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

2) Why do you keep recalculating the last row. Like:

Please Login or Register  to view this content.

3) Copy and paste in one line:

Please Login or Register  to view this content.

can be:

Please Login or Register  to view this content.

4) Calculate in memory and only print the results

5) This isn't going to increase efficiencey but just for your own personal knowledge, unless you are going to be skipping rows then you can lose the "Step 1" in lines like this:

Please Login or Register  to view this content.

6) Turn calculations off and turn them on only when they are needed to calculate a worksheet function that will be used later in the code.

7) As for better coding practices, use:

Please Login or Register  to view this content.

instead of:

[CODE]Range("A1").End(xlDown).Row[/CODE[

so that blank rows don't throw the code off.

Hope this helps.

abousetta

Originally Posted by a_driga

Secondly is there any relation having a macro running on a large file (>10 MB) with its speed?

10 MB should not be a problem for most systems to handle. I work slighly bigger files and notices that sometimes the speed start to slow down when its closer to 100 MB and using over 2 MB of RAM, but I am using a 64-bit office that allows the use above the 2 MB limit in 32-bit systems and I believe that all Excel 2007 versions were 32-bit. Usually the culprit is poor coding rather than file size and you would be surprised how much faster code runs when it is efficiently optomized.

Hope this helps.

abousetta

If you don't need a formula in a cell, but just the results of the caluclation then you can use a variable to calculate in memory rather than in a worksheet.

For example you can put this in a cell (e.g. B1): =A1+A2

Or you can write:

Please Login or Register  to view this content.

Now the calculations of adding A1 and A2 is done only in memory and only the results are printed to the worksheet. For simple calculations you won't see a difference, but for volatile functions or complex functions, it can make a very big difference.

When you get more advanced you will be able to do do a range of calculations, put the results into an array and then print the array all at once to the worksheet.

For example, let's say we wanted to calculate the sum of B:Z and put the results in A. As formulas go you can write:

A1 = B1:Z1
B1 = B2:Z2
C1 = B3:Z3
etc. (up to let's say 10,000 rows)

But you can also do this in memory and print the results all at once:

Please Login or Register  to view this content.