VERY SLOW Do While Loop Macro

[cocacrave]

Hi,
I have a macro that looks like this:

Please Login or Register  to view this content.

It takes about a minute to finish the macro loop.
It is going through a table of 270,725 cells.
Is there any way to speed this up?

[PCI]

In general manner to go faster avoid direct access to cells.
Copy the complete used range to an array, then treat the data inside the array, then copy back the modified array to the range.
If you could attach a short sample of your data, it will possible to prepare an update

[TnTinMN]

In general, I agree with PCI concerning using an array. However, you may get adequate performance by just restructuring your variable definitions a bit.

You are defining most of the variables a Ranges.

Please Login or Register  to view this content.

However all you appear to be interested in is their respective text value. These ranges all are from row 2 and are not modified by the code, hence their values remain the same during execution. To prevent repeated access to these Ranges in the "Select Case" statements, these variable can be redefined as String.

Please Login or Register  to view this content.

[cocacrave]

TnTinMN,
Thanks for your help. I tried it out but the macro is now 3x slower. Original took 1.5min, yours took 4.5min.

PCI,
I tried to make it in an array, and I've done that part successfully. However, I'm having trouble looping through these array.

Please Login or Register  to view this content.

I've tried to run it but it ran over 10+ min when I cancelled it.
When I cancelled it, it gave me an error "Run-Time error '9': Subscript out of range" and highlighted line "Select Case arrClass(r, c)"
I added "*" comment on the highlighted line.

Also, I added "**" comment on lines ".Cells(r, c + 1) = vbNullString" lines where I don't know if that is the reason why it is taking so long.
Is there a way to make arrClass(r, c + 1) = vbNullString and when loop finishes, it updates the table to equal the array?

Thanks guys

[Sa DQ]

Redim Arr(1 to 1, 1 to 10) As String
 Dim J%
 
 For J = 2 To 20 Step 2
    Arr(1, J/2)= Cells(2, J).Value 
 Next j 

[cocacrave]

Sa DQ,
Sorry, I don't understand that code... How does it apply here?

[cocacrave]

I figured it out how to array and put back to sheet. And now it takes 7seconds (from 1m30sec).

Please Login or Register  to view this content.

[TnTinMN]

TnTinMN,
Thanks for your help. I tried it out but the macro is now 3x slower. Original took 1.5min, yours took 4.5min.

Oops. I used the Text property on the Range and that is slow as it needs to format the value.

This is probably not needed, but here is the revised code in case you want to test it against the array method. I also change the Mid function to use MID$ as that is supposedly faster.

Please Login or Register  to view this content.

[PCI]

"I figured it out how to array and put back to sheet. And now it takes 7seconds (from 1m30sec)."
Good news
You could also see ( NOT TESTED here )
change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and use

Please Login or Register  to view this content.

and after see with

Please Login or Register  to view this content.

In the way to did you save memory but perhaps a bit slower