hi guys
i have the following codemy issue is that the when i run the code it take it too long to be completed , and more then once it cause the workbook to stop working ans shut off excel , no idea why, is that because the size of the range D9:R6000 that the code need to verify or what ?? is there any way to improve that code so it will not take it so long to run ?
Please Login or Register to view this content.
yes the fact that yo are looking at 15 x 6000 = 90 000 cells will slow any pc down
try addingwhere "nextcell" is a lable bust before the next linePlease Login or Register to view this content.
Please Login or Register to view this content.
this will give a shortcut on blank cells
Last edited by evertjvr; 08-02-2015 at 06:10 AM. Reason: Correction
Hi,
Is this something you want to repeat on many occasions? If not why not just autofilter the data for first percentages and then NOT percentages and either manually format the cells or use a macro that looks at .SpecialCells(xlCellTypeVisible) and formats the whole filtered block for you.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Try it like this and see if this makes any difference......
Please Login or Register to view this content.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Add this line
afterPlease Login or Register to view this content.
This will restrict data selection to non blank cells..Please Login or Register to view this content.
Also do not format cell at each iteration, try to collect cell ranges to array of range & then format it in one go..
Cheers!!
hi evertjvr than you for your suggestion, my first question is where in the code i should place the "if cell = "" then goto nextcell" ? secendly i putt the next cell just before next but i got the message sub or function not defined, ay idea what to do ????
hi sktneer
thank you for your help , when i run your code i get run-tie error 13 type mismatch on the following line- " If IsNumeric(cell.Value) And cell <> "" Then" any idea how to resolve that matter now the code take 10 seconds until i get the debug message
If your code was running without an error before making these changes, it shouldn't produce any error with the changes applied because the only line was added in the For loop is cell<>"", otherwise all is same as your original code.
Try changing few lines of your code to this......
And once you get an error see the status bar to know the address of the problem cell on which the code is working currently.Please Login or Register to view this content.
Hi Richard no it is only one time that i need to do when i open the work sheet and select the curacy i need to work with, sorry didn't understood your suggestion can you be more explanatory sorry not so good with VBA
ok let me try it and see do you want me to send you a sample of my file so you will understand what i am looking to do maybe you can come up with improved code ?
hi excelliot
i tried what you said but it take it too long first i get excel no responding and it does not run the macro what is wired for me when i run the code only in the range of 1000 cells it run fast and good (ie range(D2:R1000) don't understand why
Last edited by Pilot5000; 08-03-2015 at 05:35 AM.
i have done what you wore but still get the same run time error on the same line, but now i saw the problems , the reason it get the error message is because in same cell i have #value and not a number , the reason is in one cell i have number , and in the other cell i have Text "No Activity that Year" and the third cell is the sum of the other two cells , any idea how to resolve that ??????
Last edited by Pilot5000; 08-03-2015 at 06:11 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks