LinkBack URL
About LinkBacks
Hi all,
the below code works but is VERY slow. I am sure it has to with a for each...next inside a for loop...next.
Is there a better (and faster) way this can be set out?
Please Login or Register to view this content.
Hi all,
the below code works but is VERY slow. I am sure it has to with a for each...next inside a for loop...next.
Is there a better (and faster) way this can be set out?
Please Login or Register to view this content.
Why do you need the outer loop anyway ?
You just repeat the same code over and over again.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
the outer loop is required as the conditions change when the iteration occurs. Should the outer loop be removed then?
I don't know what your data looks like and what you're trying to achieve,
but assuming you have 1000 rows of data you then loop a thousand times 1000 rows of data. You can imagine why the code is SLOW.
Oh ok, then that makes sense as to why its so slow. I will post a sample
I have attached a sample and I have had to de-sensitive the data.
Essentially, the procedure goes through a series as shown in col U:V. This number is the divisor in col K. If the bank is bigger than the start bank, then the series starts again from 20. If not, it goes to the next number.
I'm hoping there is a more simpler way to achieve this?
If I run the code on these numbers all is set after the first outer loop. After that nothing changes anymore with each outer loop
So I still don't see the purpose of the outer loop.
got it, thank you. I removed the outer loop and it seems faster. I will do some more testing.
Some tips for improving loops
- Any actions that can be done outside the loop, keep them outside the loop
- If manipulating worksheets, turn off events & screenupdating before the loop (Turn on again after) (You could also try changing the Calculation method but it shouldn't be necessary after doing these two)
- If manipulating excel ranges, look into if you can achieve your desired outcome using arrays instead. If you can, you will speed up your code by a few orders of magnitude. It is much faster to read the range into an array, manipulate the data inside the array and paste it back when done. I have achieved improvements of up to 60x faster.
*******************************************************
HELP WANTED! (Links to Forum threads)
Trying to create reusable code for Custom Events at Workbook (not Application) level
*******************************************************
This seemed to run a little faster on your small sample...
Please Login or Register to view this content.
thank you! That also works quite well.
However, just one further question. I seem to get a number in col K in the next blank row. I have tried to remove it but nothing seems to work?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks