Improve code using ADODB.Recordset when populate data in listbox to make fast

Nie podoba mi się Redim Preserve w pętli FOR.
1. Usuń Redim Preserve w pętli FOR
2. Przed For i = 0 i po Next i dodaj po 1 linię
3. Poprawiony kod

Please Login or Register  to view this content.

@hungt
I don't understand your comment , sorry !

Also...No need for listbox formatting loop...do that in the first loop...

I don't think this could be main reason to do that.
even if I delete this part will continue same problem!

this is really better sintek .
but should also be without writing dates in textboxes.

Originally Posted by Mussala

@hungt
I don't understand your comment , sorry !

The point is not to Redim PRESERVE in the FOR loop. I gave a different code. Did you check my code?

The point is not to Redim PRESERVE in the FOR loop. I gave a different code. Did you check my code?

Mismatch error

Please Login or Register  to view this content.

subscript out of range

Please Login or Register  to view this content.

by the way I would loop through sheets just before FRM sheet , not all of sheets except FRM sheet as your code does it.

Mussala,
If you are using SQL to fetch data, then add a dates filter to it as well, so you don't have to filter them in the "For...Next" loop, it will be faster.
In your case, to keep it simple (and what you are already used to), the simplest modification to the SQL query is, e.g.:

Please Login or Register  to view this content.

So why not upload a sample file representing your actual file so no time is wasted again...
Error is due to these other sheets not presenting same data setup...

just I copy random data to show how work for many data.

The above is exactly same as other...
3 tabs and FRM???

yes but I said

when add about 30 sheets and 5000 rows for each sheet.

Change 10000 to bigger amount...100000

it's fixed.
can check this comment, please?

by the way I would loop through sheets just before FRM sheet , not all of sheets except FRM sheet as your code does it.

Originally Posted by Mussala

Mismatch error

Please Login or Register  to view this content.

You complained that the code was slow, so I suggested moving Redim outside the FOR loop. I didn't check the correctness of the code because you didn't complain about the error. The quoted line is your code, I didn't suggest it. For me EOT.

I add new sheet after FRM sheet and show mismatch error

Please Login or Register  to view this content.

You complained that the code was slow

yes

so I suggested moving Redim outside the FOR loop

maybe this reason causes error.

I didn't check the correctness of the code because you didn't complain about the error

no need to check i because the original code works without any error.

If you are using SQL to fetch data, then add a dates filter to it as well, so you don't have to filter them in the "For...Next" loop, it will be faster.

thanks for advice, but truly I don't use SQL and I don't know how link with SQL despite of I hear of that.

thank you so much, sintek.

Originally Posted by Mussala

... but truly I don't use SQL...

Did you write the topic title and content of the first post yourself or did someone write it for you ?