+ Reply to Thread
Results 1 to 23 of 23

Trying to speed up macro via formula or code (Countifs)

  1. #1
    Forum Contributor
    Join Date
    06-28-2018
    Location
    New York
    MS-Off Ver
    MS Office 2010
    Posts
    178

    Trying to speed up macro via formula or code (Countifs)

    Hi all,

    I currently have a macro that is working but the below code is slowing it down a bit. Basically I have order numbers and locations (geographically speaking).
    I am using the formula below to try and identify instances where the same order number (stored in named range called "OrderNum") is found across multiple locations (locations are coded by number and stored in a named range called "Location"). The macro is then entering the formula down ~21k rows in column AB starting with row 4. This range is dynamic and can change. Any assistance either through code or formula would be greatly appreciated. Thank you.



    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,729

    Re: Trying to speed up macro via formula or code (Countifs)

    Please attach a short EXCEL file sample, this is the only way to make macros clear and efficient
    - Battle without fear gives no glory - Just try

  3. #3
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,729

    Re: Trying to speed up macro via formula or code (Countifs)

    Note
    Please Login or Register  to view this content.
    is used to get the last row for column 3 is it OK while the working column is "AB" ?

  4. #4
    Forum Contributor
    Join Date
    06-28-2018
    Location
    New York
    MS-Off Ver
    MS Office 2010
    Posts
    178

    Re: Trying to speed up macro via formula or code (Countifs)

    Here is a sample that should help. The macro works, just slow. Please note the named ranges I am currently using are not in the sample, however i have indicated the named ranges above each column. Thank you for any help with this.

    Also to answer your question I am using column 3 as my reference since that appears to be the column that consistently has no blank rows and appears to reflect the full range.
    Attached Files Attached Files
    Last edited by nickytraps; 10-04-2018 at 12:14 PM. Reason: Did not reply to previous post

  5. #5
    Forum Contributor
    Join Date
    06-28-2018
    Location
    New York
    MS-Off Ver
    MS Office 2010
    Posts
    178

    Re: Trying to speed up macro via formula or code (Countifs)

    In reality I would be ok with eliminating column AB (potential location reclass) altogether. I am really using that as a first step in getting to the real needed result which is in column AC (location reclass). Currently the code/formula are separated.


    Please Login or Register  to view this content.
    Last edited by nickytraps; 10-04-2018 at 12:22 PM. Reason: adding code

  6. #6
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,729

    Re: Trying to speed up macro via formula or code (Countifs)

    Also to answer your question I am using column 3 as my reference since that appears to be the column that consistently has no blank rows and appears to reflect the full range.
    Column 3 means the named range "Location" is it ?

  7. #7
    Forum Contributor
    Join Date
    06-28-2018
    Location
    New York
    MS-Off Ver
    MS Office 2010
    Posts
    178

    Re: Trying to speed up macro via formula or code (Countifs)

    Yes that is correct. The below are what I set up in the actual code.

    Please Login or Register  to view this content.

  8. #8
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Trying to speed up macro via formula or code (Countifs)

    @nickytraps you posted a sample without any macro in it. .xlsx files do not contain macros.

    EDIT: What I can tell you from the sample you posted, is you likely have multiple issues in the file that contribute to speed issues. For example you have 43,833 styles in the sample you posted. The default number is 47. This can place a burden on the file in many ways. This is why your sample is ~300KB for what is really 2 blank sheets and a sheet with less than 10 rows used and 27 columns. Without the excess styles this file is 14KB, so a difference of 95% in the sample file size...thats not a small difference.
    Last edited by Zer0Cool; 10-04-2018 at 01:42 PM.
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  9. #9
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,729

    Re: Trying to speed up macro via formula or code (Countifs)

    Try next code and comment
    It is only for the part of your request, we will see colułn "AC" if it is positive
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    06-28-2018
    Location
    New York
    MS-Off Ver
    MS Office 2010
    Posts
    178

    Re: Trying to speed up macro via formula or code (Countifs)

    Thanks PCI. I am getting an error message related to line 14 of the code "ONb = OrderNum(I,1)" Run-time error '6' Overflow.

  11. #11
    Forum Contributor
    Join Date
    06-28-2018
    Location
    New York
    MS-Off Ver
    MS Office 2010
    Posts
    178

    Re: Trying to speed up macro via formula or code (Countifs)

    @ Zer0Cool

    I was not aware of that. Is there a way to remedy that. As I mentioned the complete workbook is quite large and imports data into a few other worksheets from a system generated report.

  12. #12
    Forum Contributor
    Join Date
    06-28-2018
    Location
    New York
    MS-Off Ver
    MS Office 2010
    Posts
    178

    Re: Trying to speed up macro via formula or code (Countifs)

    Thanks again all for the help, it is greatly appreciated. Below is the complete original code.

    Please Login or Register  to view this content.

  13. #13
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,729

    Re: Trying to speed up macro via formula or code (Countifs)

    I am getting an error message related to line 14 of the code "ONb = OrderNum(I,1)" Run-time error '6' Overflow.
    What is the value for I, how many rows there is?

  14. #14
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,729

    Re: Trying to speed up macro via formula or code (Countifs)

    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  15. #15
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Trying to speed up macro via formula or code (Countifs)

    Quote Originally Posted by nickytraps View Post
    I was not aware of that. Is there a way to remedy that. As I mentioned the complete workbook is quite large and imports data into a few other worksheets from a system generated report.
    Identifying this particular issue with styles is easy, removing them requires a macro or a 3rd party program (as Excel only allows deleting 1 by 1).

    To see the Styles are in excess yourself, go to Home | Styles | More (the downward pointing arrow with a line above it to the right of the styles list). This will expand out a list of the styles in the file. In your sample (and thus likely your file) you will see many duplicated names in this list and need to scroll down the list. Compare that to a new clean blank file with the 47 defaults and the difference is obvious.

    To get an exact count open the VBE (Developer | Visual Basic or Alt+F11) with the problem workbook active and type the following in the immediate window (bottom pane in the middle, if not visible its Ctrl+G or View | Immediate Window):

    Please Login or Register  to view this content.
    This will output the count under it for you, for me it was 43,833.

    I created an addin to count and remove these but am not ready to share my code for it publicly. Your best bet would be to Google the matter or create a thread here asking for help removing excess styles. What I can tell you is that in this particular file, you are fortunate that none of them are corrupt styles and thus most VBA approaches using the built in Styles collection are likely to work just fine for clearing these out.

    As for how files get this way, its generally through copying and pasting between files, creating a bunny rabbit effect in which styles duplicate over and over again (hence the duplicate names). This can be avoided by copying whole sheets properly (using the sheet Move or Copy... feature and by copying ranges of cells using the least amount of formatting carried over as possible (IE paste special values vs standard paste which literally pastes everything, including underlying styles, links, named ranges, other formats, etc.).

    In short, best practices for moving/copying data between files and sheets will prevent the problem.

  16. #16
    Forum Contributor
    Join Date
    06-28-2018
    Location
    New York
    MS-Off Ver
    MS Office 2010
    Posts
    178

    Re: Trying to speed up macro via formula or code (Countifs)

    I changed the Dim ONb As Integer to Dim ONb As Long. There was a longer delay and then the same run-time error '6' occurred.

  17. #17
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,729

    Re: Trying to speed up macro via formula or code (Countifs)

    And what is the value for I when the error occures

  18. #18
    Forum Contributor
    Join Date
    06-28-2018
    Location
    New York
    MS-Off Ver
    MS Office 2010
    Posts
    178

    Re: Trying to speed up macro via formula or code (Countifs)

    Quote Originally Posted by PCI View Post
    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    I tried this and still received the same error. Run-Time '6'

  19. #19
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,729

    Re: Trying to speed up macro via formula or code (Countifs)

    How many rows do you have ?

  20. #20
    Forum Contributor
    Join Date
    06-28-2018
    Location
    New York
    MS-Off Ver
    MS Office 2010
    Posts
    178

    Re: Trying to speed up macro via formula or code (Countifs)

    Currently there are 21,364. This could change in a particular month so it's dynamic.

  21. #21
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Trying to speed up macro via formula or code (Countifs)

    From looking at your macro code (and without me having tested it) here are some recommendations to help speed up code.

    * Explicitly state objects. In other words avoid using things like "Range()" without the parent sheet/workbook. This doesnt mean you need to type it out fully explicit every time. Declare range, worksheet, even workbook variables and cascade down setting them.

    Please Login or Register  to view this content.
    In the above, each object only gets evaluated 1 time, when its set, instead of each time you reference it implicitly. You also dont have to use With statements as much any longer, as workbook and sheet are implied with rngStart (or whatever you call/set range to) so that 'rngStart...Name' is the same as 'workbook.worksheet.range...name'

    * Avoid using .select and .activate as they are almost never required to act upon objects. For example, leveraging the above, "ThisWorkbook.Activate" would not be required if you declare and set both workbook objects. .select and .activate can slow down a macro greatly.

    * Avoid repetition when a result could be stored and reused. Ex: .End(xlUP) being repeated multiple times. Calculate it 1 time, store it in a variable, and then use the variable instead. Same for Rows.Count.

    * I dont see the purpose of dumping the array "Ary" into a Dictionary and then back again. I also would have declared an object, set it to the CreateObject and then used a With statement with the Dictionary object variable (late bound) if I needed the dictionary.

    Seeing the macro as part of a macro enable file and being able to follow its logic may reveal a better approach to achieving your goal or more specific ways to speed it up. If you could post a functional sample with the code included in the file itself.

    * EDIT: Also using constants for repeated literals like 3 in your code can improve speed as well. generally anything I will reuse more than 2-3 times throughout my code I declare as a constant.
    Last edited by Zer0Cool; 10-04-2018 at 02:50 PM.

  22. #22
    Forum Contributor
    Join Date
    06-28-2018
    Location
    New York
    MS-Off Ver
    MS Office 2010
    Posts
    178

    Re: Trying to speed up macro via formula or code (Countifs)

    Thank you Zer0Cool, I really appreciate the information. I am new to coding and have no formal training, so trying to figure this stuff out as I go. I will attempt to make some of the recommended changes and tweak what I can figure out and see what happens.

    I also saw the style count was exactly what you stated. I found some info (code) that might help to clean it. So I will test that as well.

  23. #23
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Trying to speed up macro via formula or code (Countifs)

    Sure thing. As an aside, if you are interested in learning VBA, then you may considering getting a resource to help teach you the fundamentals to establish a good base using best practices. Its much easier to learn to do things the "proper way" (in quotes as everyone has their own take on what this is) then it is to unlearn bad habits later on. I personally like the Excel Bible books by Walkenbach, I think they give a good foundation for both Excel and starting with VBA. Online there are a ton of resources, the problem is finding the good resources vs the bad ones.

    I, having done VBA programming professionally in the past, have had some of the best practice stuff "beat" into me. I declare everything (and aim to use Hungarian notation which may be a bit old school), always use error handlers, comment code, try to do things modular and efficiently, etc.

    In any case if you can post a functional sample file (with the macro code included in the file) I can run your code on it and test the speed of we can see if we can make it more efficient.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Speed up/Streamline countifs/sumifs code/formula
    By nickytraps in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2018, 05:16 PM
  2. Reduction of code lines to speed up macro
    By Sc0tt1e in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-24-2016, 11:00 AM
  3. [SOLVED] Reduction of code lines to speed up macro
    By Sc0tt1e in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-24-2016, 11:00 AM
  4. How to improve the running speed of this VBA macro code?
    By sellim in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-24-2012, 01:45 PM
  5. Way to speed up countifs using VBA?
    By gazza365 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-18-2011, 02:23 AM
  6. speed up vba code within excel macro
    By MOSHEBS in forum Excel General
    Replies: 3
    Last Post: 09-25-2011, 08:23 AM
  7. how to speed up macro code execution?
    By sharmanjali87 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-03-2010, 06:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1