Quickest way to iterate through a range

I have a matrix with 58 columns and 766 rows, so 44428 cells in total. This matrix is populated with forecasts/statuses for 'requirements'. The status could be "In Stock", "No Order", a date "12/11/17" or something else. The cells are also colour coded and formatted. I use a series of checkboxes on a userform to toggle whether or not I want these cells to be displayed. The following code then iterates through every cell in the range and tests it based on cell format and adds matching cells to a variable range before displaying the entire row/column of the matching range.

Now this works fine with only one or two criteria ticked, taking a second or two but with more boxes ticked (there are eight boxes) it can take up to 10 seconds to refresh. This is a live application so I need it to be more efficient so I'm looking for a more elegant solution. I realise here I am testing each cell up to eight times so maybe I can do something to dynamically change the range as it iterates.

Another thing I thought was to assign variables to the colours and then add those to an array so it only has to iterate once regardless of the number of boxes checked but this is just slightly beyond my level of understanding

Any suggestions? Thanks in anticipation

Please Login or Register  to view this content.

Originally Posted by buran

make it the other way around - loop once over dRng and then do checks.

Could you please expand on this? I don't understand, sorry

Originally Posted by buran

Also, with small amendments the code could be reduced significantly

Again, could you please point me in the right direction. I know it's not the most elegant solution, I just need a few pointers.

Thanks for looking

Hi,

Thanks for the solution Buran. I'm afraid it's not working although your method looks sound. I think it's an easy fix but I've not been able to figure it out. When I step through the code ctrl = Nothing so there appears to be an issue with the loop through the userform controls. Maybe the userform isn't being initialised or something?

The following test code workd fine though.

Please Login or Register  to view this content.

Ok I found the bug

Please Login or Register  to view this content.

should have been

Please Login or Register  to view this content.

The code now executes however isn't particularly fast and also returns unexpected results, i.e. selections that should return results show none.

This gives me a good start and an alternative approach so thank you for your help so far Buran

Thanks

Ok so now I know why it's returning unespected results. Only the cbx_fs checkbox is being included.

Additionally, it seems that the current version may be slow because for each c in dRng the userform controls are being looped. This only needs to be done once to set the my_colors variable. If we remove this from the outer loop it should be much quicker.

Hi,

I had already added the ctrl tags so that was not the problem. I've also added another checkbox and separated the two colours from checkbox9 to remove that complexity.

Where I'm stuck at the moment is I can't see anywhere in your code that the ctrl.tag is added to the variable my_colors so when the loop is iterating through each c in dRng the variable is not set so no matching cells are found. The only cells found are those with bold font.

So I have researched variable arrays and made the below changes. Firstly, I iterate through the checkboxes and add all selected tags to the array. I've checked this in the immediate window and the variable my_color() reterns the relevant variable. I have separated this from the dRng loop so it does not need to reset the variables for every loop.

The next part is where it falls down. For each c in dRng it should check the c.interior.color against each color variable in my_colors. It does this but never finds a match. I believe this is because the my_color() variable taken from ctrl.tag is a string and the c.interior.color is a colorformat.

Any ideas? Am I looking at this in the wrong way?

Please Login or Register  to view this content.

OK, i've got it to work now. The my_color variable type was wrong. It should have been "double". So the code now works, although isn't particularly fast. Similar in fact to the previous code, approximately 10 seconds if all checkboxes are ticked

Originally Posted by buran

again, can you upload a workbook. it will be much easier

It is full of sensitive data, I will have to cleanse it. Please bear with me.

Hi,

I've attached a cleansed workbook.

Please can you take a look? I want to set the variables based on the checkboxes then iterate through the cells and if they match the variable values then display those cells.

There are lots of other controls in the userform too.

Many thanks

Hey Buran,

Originally Posted by buran

There is some code I'm not sure I fully understand why you need it

Which is the code you're referring to? This is a cleansed development version so there is some test code in there which I was using for debug purposes. There is some code which retains and reapplies the filters. Now I look at it, I'm not sure I need it anymore.

The report is a logistics overview of parts required for manufacture of major units. The first 20 are now complete so there is some time wasted iterating through these cells. One way I plan to speed it up is by doing a precleanse of the dRng with something like for each column in dRng, if(countif (dRng,"Clear")=dRng.rows.count then .entirecolumn.hidden=true. Then set dRng to drng.specialcells(xlcelltypevisible) before calling the main macro.

I like your idea of iterating row by row and I will have a think about how to implement it.

One final edit I have also made since I uploaded the file yesterday is below. Adding an Exit For into the colour check loop as beforehand it was continuing to check all colors in the array even if it had found a match.

Please Login or Register  to view this content.