Very slow to respond / run time error "9"

[Mouctar]

Hello everyone! With the help from of one VBA guru in here below code was created for what i need to do my job. However, when i applied the code to between 100 to 1000 lines it works fine but if it more than that then it takes one hour or more to completed and there nothing i can do within that period. Also, if i open new workbook and applied for the same volume of data then i get error (run-time error "9") message on a= sheets("Sheet1").cells(1).currentRegion.value. Please advise what i need to do here?

Please Login or Register  to view this content.

[Keebellah]

I susggest you also disable events and do not to reset them at the end.
The runtime error 9 is a subscript out of range, in other words invalid data or range selected, value incorrect, etc.

[Mouctar]

Hi, Thank you for your help. However, i am not sure what you mean by that, can you please give more details?

[CK76]

Also, looking at your code you are creating multiple dictionaries within loop. This will slow things down.

Break your code down into segments and find out where the bottle neck is occurring (use Timer function).

As for Run-Time Error 9... most likely culprit is that the sheet named "Sheet1" does not exist.

[Keebellah]

It'simple "Sheet1" does it exist? Check !

[Mouctar]

Hi CK76, can you are you able to break the code by segment please, as i am VBA beginner?

run time error "9" Subscript out of range. The sheet name is "Pen Reports" but i have used this name where Sheet1 is and i am still getting same error

Thank you for looking at this

[Mouctar]

Hi Keebellah, The sheet name is "Pen Reports" but i have used this name where Sheet1 is and i am still getting same error

[CK76]

Upload sample workbook with small data set for some testing.

FYI - The code uses abundant short cuts and short forms, and looks to be written by someone comfortable with VBA. I assume you got someone else to write the code or inherited this workbook? I'd recommend replacing some of single letter variables with more meaningful string. So that you can identify what each step is doing.

[Keebellah]

This code is NOT correct

Please Login or Register  to view this content.

Should be something like this

Please Login or Register  to view this content.

[Mouctar]

Yes, that's true someone wrote the code for me and i only added few lines to it to try to make bit faster base on my research. Please note the code work fine when it use for between 0 to 2000 lines. However, my main files i am currently using on it is up to 70565 lines and it takes more than one hour to completed. I have tried several methods based on my research but nothing is working and i am hope someone might be able to advise what i need to do here. Please see below link

http://www.excelforum.com/showthread...14#post4554014

[kev_]

The code is going through a lot of steps and there are 70,565 lines - so I am not surprised it takes time to run.
If it takes 30 seconds to run 706 rows, then it will take approx 75 minutes to run 70,565 rows
What I would do is to check whether the timings are in proportion to the number of rows.

You may be hitting some memory issues as the file size increases.

Try something like:
Create a totally new file and only paste in the used area from 7,057 rows - make sure you only copy/paste the used range - ie no extra rows or columns - do not copy complete columns or rows
Totally new files have no junk and so the memory allocated should match the area used
Run the macro - if it completes in around 6 to 7 minutes then it is comparable.

My gut feeling is that the time taken is down to sheer volume, but it's worth a quick check.

[CK76]

@ Mouctar

How many unique Wage Type Long Text are there in actual data set?

Bottle neck in the code is dependent on above. Since each unique Wage Type Long Text adds new dictionary objects and creates new sheet.

Keeping unique Wage Type Long Text to about 10. 100K + Rows of data took about 20 sec to process on my machine (i5-4200M @ 2.5GHz, 16 GB ram, 32 bit Excel).

Then just for fun, used CHAR(64 + RANDBETWEEN(1, 26)) to generate random Wage Type Long Text and tested...
It ran for more than 15 min and was still running when I terminated the process.

[Keebellah]

Like I mentioned, Sheet1 does NOT exist
I don't know what you're trying to do but the code runs in 8 seconds with the file from the link.
I pasted the macro code and it runs, I added progress information on the statusbar and it completes this sample in 8 seconds.
I use Excel 2010 and 2016 no problems

And yes, with 2010 it runs in 1 second flat

[Mouctar]

Hi Kev,

Thank you for your respond and i have follow your advise and it did work with small volume. However, the macros is fast with the code first created. Please see link above

Thank you

[Mouctar]

Hi Ck76,

Thank you for your help. There are currently 130 Wage type Long Text but with duplicate it 66509.

[Mouctar]

Hi Keebellah,

Thank you for all your help and i have applied the code attached to my main files and the running has improved; as it takes now just little over half hour and i don't get any error message when use it to new workbook.

Thank you and much appreciated

[Keebellah]

Which version of Excel did you say you are using?
How can I test larger file for you?

[Mouctar]

I am using Excel 2010

I cannot post the files as its over limit bites. I wish i could load the file for you to text on it.


Thank you

[kev_]

Thank you for your respond and i have follow your advise and it did work with small volume. However, the macros is fast with the code first created. Please see link above

I am not sure I understand your answer.
How long did macro take to run in a "new" file with 7,057 rows?

[Keebellah]

@mouctar: did you read my PM ?
If you still want me to test it let me know.

[Mouctar]

Hi Kev,

Sorry it took 40 second with new file and 7056 rows

Thank you

[Keebellah]

So it's working fine? Nice to know that @Kev solution worked

[Mouctar]

Hi Keebellah,

I am using Excel 2010

Yes, i would love to if you can test it but i have tried to load files in here and it appears that the files is too big. i am not sure how you can test it . Any idea?


Thank you

[Keebellah]

I sent you a PM (Personal Message) with my email address.
If you would like me to take a look mail it to that address, but you can also compress the file (using zip or rar) right button context menu in Windows explorer.

[Mouctar]

Hi Keebellah, i have now sent the file for you to test on it

Thank you

[kev_]

it took 40 second with new file and 7056 rows

- so it should take less than 10 minutes for 70,000 rows - which is a lot better than over an hour!! Excellent

Files that are regularly updated can end up with unwanted (sometimes totally invisible) clutter - Excel may be allocating a huge amount of memory to something because of some historic legacy and there may be no evidence of it whatsoever in the body of the spreadsheet.

[Keebellah]

Hi mouctar,
I ran a test without changing the code I sent you.
You file has 70557 rows of data:
The first loop took 45 minutes and 47 seconds,
The second loop took 32 seconds.
I'll see if there's a way to make it less consuming.
I don't know when I'll have the time but I'll keep you posted.

[Keebellah]

I've attached the file in compressed mode (zip).
I haven't been able to speed it up but I now know what you're doing.
What I did do is add two small routines that update the status bar as you go.
It shows the row being processed, how many % of the total processed, the time elapsed and the estimated time to completion.
What you can see is that as you process more rows it starts slowing down because each value has to be cross checked against the array in memory and this one keeps growing.

My idea would be to directly create the worksheet and fill it with the data as you go and I think this would speed it all up.
I have to see what you're doing to see an extra column added in some cases and others not.

So all I have sent you now is the same file with the 70000+ rows but with the progress showing in the statusbar, I always include something like this in my files so you know
it's doing something, it only stops if Excel says 'Not Responding ...' but that is an incorrect message, Excel is still working in the background and then all you need is patience

[Mouctar]

Hi Keebellah,

Thank you so much for all your help and much appreciated. The extra bits you have added to it is very important, as i know where the macro is up. As your advise, i need to be patient because the files is big.

You are a star

Thank you

[Keebellah]

I tried some ideas but it doesn't really help much.
The last log (see thumbnail) shows the results.

Only 17498 of the 70557 are split the others do not have a '('

[Keebellah]

I made some changes and came to 25 minutes, but the results are not the same

I get much more results (duplicates) and some that I could not find in your results.

[Keebellah]

I have included the second test file, this one with the macro I have here took 25 minutes but the data is not grouped.
What does surprise me is that data wich should be there is not there (in your version 001) but the totals are correct / the same.

Check and compare sheets NEST AE and filter Mr Kar you will see that you miss ID numbers my version 002 is with the faster macro

[Mouctar]

Hi Keebellah,

How are you and thank you for your help. Just quick question. Where can i amend the code if i add or deleted columns?

Thank you

[Keebellah]

Hi, I did not write that part, that's part of the code you had and posted with the file, my guess is that if you add a column it will be taken into account as well as deleting one.
Have you tested it?

[Mouctar]

Hi,

I have just tried but the i am getting lot of errors. No problem thanks for looking into it and will get back to the person who help me create it.