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

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" ?

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.

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.

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 ?

@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.

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.

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?

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Originally Posted by nickytraps

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.

And what is the value for I when the error occures

How many rows do you have ?

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.

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.