Hi guys, quick question about speed of a workbook that has VBA code in it.
The workbook worked fine in Excel 2010, but now that I got an upgrade at work to Office 2013,
the book takes forever when changing values.
Any hints to make this code work faster? Or is it a 2010 vs 2013 thing?
Thx for any advice, will be appreciated !!
Please Login or Register to view this content.
Kind regards
Kristof
Hello louvaek,
Try these macros and let me know how they perform.
Please Login or Register to view this content.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Hey Leith,
Thanks for taking some time to look at the code and offering a solution.
My code does 4 things:
1/ highlight cells that have comments
2/ change all lowcase values in uppercase
3/ Sort ranges C:I depending on input in collumn C
4/ Unhide a new row if value is added in collumn C, or hide a row when a value is deleted
After replacing the original code with yours, unfortunately all macro's have stopped working (no error, just not doing anything)
On the bright side, the sheet works very very fast now hahahaha
To explain number 3 and 4, let me try to give an example:
C D E F G H I 9 Jurgen Jurgen Jurgen Jurgen Jurgen Jurgen 10 Stefano Stefano Stefano Stefano Stefano Stefano 11 Ron Ron Ron Ron Ron Ron
Row 12 is an empty line, where user can make new input in collumn C.
The moment the input is done, C9:C12 is sorted (A to Z) en and a new line is unhiden (row 13).
(btw collumn D is just empty)
Hope this helps a bit
Hello louvaek,
Sorry about the results. I had hoped it would have worked. Without a workbook, I cannot test the macros accurately.
The macros are shortened versions of the originals you posted. The reason they were slow was due to an extreme amount of redundancy and improper error handling. There was really no reason you should have had errors in this code.
If you can post a copy of the workbook, that would be fastest way for me to test and troubleshoot the macros. Before posting a copy of the workbook, please remember to redact any sensitive or confidential information.
One sheet attached (B2019.xlsm)
This is with the original code that is now so slow in Excel 2013.
Thanks man, appreciate it !!
Try this (abomination?)
Please Login or Register to view this content.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Hello louvaek,
I downloaded the workbook you posted. However, something is amiss with the internal structure. The workbook fails to run correctly even with your original macros and froze my system twice.
Due to the aberrant behavior of the workbook, I am unable to help you further.
Xladept thanks man, works like a charm !!
Leith, thanks anyway, glad you've tried and appreciate it !
You're welcome and thanks for the rep!
*Very interesting - the code is, virtually, the same only without so many If.......End If parts
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks