Performance For vs While

[Najwa_X]

Hello All,

Which is better for my Macros performance for running through arrays While or For ? I have the feeling since I changed my loops from While to For my Macros are more efficient in terms performance as it is taking less time to give results ? or I am totally wrong ?

thx for the advise.

Najwa.

[AlphaFrog]

It depends...

While makes a repeated comparison for each loop which may or may not be expensive.

While x <> y may be expensive if x or y are objects (depending on the object type).

For i = 1 to 10000 would be inexpensive

For Each Cell in Range("A1:A10000") would be very expensive.

One thing that is expensive in a loop (While or For-Next) is repeated references to a cell or Range.

[Najwa_X]

Hi AlphaFrog,

Actually while is counter and my range is can go 1000.

While I<= X
Comparing each cell in a range of 1000

I think in that case it is better to use While.

Thank you.
Najwa.

[AlphaFrog]

Hi AlphaFrog,

Actually while is counter and my range is can go 1000.

While I<= X
Comparing each cell in a range of 1000

I think in that case it is better to use While.

Thank you.
Najwa.

Comparing each cell in a range of 1000 is the bigger issue. More than While v. For. Each time you access the sheet, it is very expensive, and it increases quickly as your range size increases

If you read the range into an array
MyArray = Range("A1:A1000").Value

Then loop through the Array with either While or For-Next, you would notice a significant improvement because you only access the sheet once to read the range.

[rorya]

If you just want to process every cell once, you should use a For loop.

[Najwa_X]

Hi AlphaFrog,

Actually this what I did I read the Range into an array and started my several comparison based on a condition and splitting the array into several arrays.

However, when my condition is not met my array value is empty and when I have to put back my array into my excel sheet I need need to delete the empty cells which is very consuming as my defined ranges goes to 1000.

I couldn't find a way where to have a clean array when the condition is not met. The upload of the file failed. Below is the code.

Thank you.

Najwa.

Please Login or Register  to view this content.

[AlphaFrog]

Have seperate row counters for the result arrays

Please Login or Register  to view this content.

[Najwa_X]

No words can express my feelings !
Thanx a lot AlphaFrog !

[AlphaFrog]

You're welcome. Thanks for the feedback.