Help improving performance?

I have a VBA function that's running very slowly, and I'm looking for suggestions as to how to improve it. The context is that the sheet has multiple sets of rows called 'components', and this function is walking through them determining which ones should be visible and which should be hidden

Here's my (probably naive) implementation:

Please Login or Register  to view this content.

I don't think the implementation of reset_component or hide_or_show_component matters much, but I'll post them if they might.

Anybody have any suggestions?

Thanks,
Aaron

Originally Posted by Richard Buttrey

Hi,
Rather than loop through zillions of cells just get the macro to autofilter your data using the appropriate criteria.

Thanks, that's very useful. Is there a way to do something similar for a property other than Visible? Specifically, I'd like to set some of the components to "disabled" by changing their text color to gray:

Please Login or Register  to view this content.

Thanks,
Aaron

Got it - I need to combine conditional formatting (for the gray text) with filtering (to hide the appropriate rows).

Aaron

OK, I thought I had it, but apparently not. Conditional formatting, as far as I can tell, is utterly and completely broken? I can apply the same rule to the same selection twice in a row and get two different results. What the hell?

Assuming that I can get it working manually, there seems to be an interaction between conditional formatting and filtering? Here's the code that I'm hoping to use:

Please Login or Register  to view this content.

Does this look like it ought to work? Column BC contains a value "TRUE" or "FALSE" indicating whether the row should be hidden; column BD contains a similar value indicating whether the row should be grayed.

Thanks,
Aaron

I'm sorry, I didn't understand that. I can't use a color set by Conditional Formatting for what?

Aaron

The problem seems to have to do with my currently-selected cell, which suggests that UsedRange doesn't do what I expected it to. If my currently-selected cell is A2 when the code runs, all my formatting is shifted down a row. That is, row 4 gets the formatting from row 3, and so forth.

Any idea how this code could be affected by the current selection?

Thanks,
Aaron

OK, so it isn't UsedRange that's behaving strangely - it must be the $BD1 reference in the conditional formatting formula. Given that I'm applying the formatting to UsedRange, which starts at A1, shouldn't that use the value of column BD for every row, regardless of the current selection at the time the macro runs?

Thanks,
Aaron

OK, here's a clue: When I run the code (with the active cell set to A3), and then look at the formatting rules applied to any random cell, I see "=$BD65535=FALSE". Oddly, even though every cell has the same formula showing up with the same row number, some rows end up formatted and some don't.

I stand by my statement that conditional formatting is broken, unless somebody can point out what I'm doing wrong.

Thanks,
Aaron

OK, I think I've got it working by a collection of funky hacks and workarounds. I have to manually hide row 1 because for some reason the filter isn't willing to hide it, and I have to mess with the selection in the code or the conditional formatting doesn't work. Here's the final code - I'd appreciate any ideas for removing some of the cruft.

Please Login or Register  to view this content.

Thanks,
Aaron

OK, after all that, I don't think I'm going to be able to use that technique. It's a good one to know, but the problem is that I would have to add a "visible" flag to each row. The flags would have to be hidden, which means that if the user inserts a row, it won't have a flag, and they won't know why it's not working. I'm putting a lot of effort into making this sheet difficult for users to damage, so that's not an acceptable compromise. I tried using a function to identify the row in the component header that has the "original" flag value, but that makes the whole sheet run ridiculously slowly, since it has to execute that function a thousand times any time any cell changes.

Any other suggestions?

Thanks,
Aaron