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