Speed up hiding/unhiding rows

See how you get on with these:

Please Login or Register  to view this content.

Try this

Please Login or Register  to view this content.

Hi,

I am using Colin Legg's code which seems a bit faster that the previous code (see top of Thread). Is it possible to adapt your code to include blank cells but also cells containing a Zeo ("0")?

The range of cells is B4:B81. I am attaching a sample. The workbook inludes 11 sheets for different categories, but I am attaching just the Awards sheet.

Thanks for your help.

John

Hi John,

The criteria I used (" " and 0) were just based on your initial post. I see that you have made some modifications to my code in the attached workbook. Before we talk about hiding division headers let me just check that the modifications are working correctly for you. For example, at the moment the code will hide Row 27, even though there is an entry in D27. Is that what you want?

Hi Colin,

No, it there is an entry in Columns B,C or D the row should NOT be hidden. The range in the code of the Awards sheet should be D4:D81 as there will be always a band, but not always Aux (C) or Perc (B) competition.

Thanks for catching that!

John

Okay, I've had a look at your worksheet and I think we can do this quite easily, but with a slightly different approach.

In F3 I added this formula and then filled it down to F80:

Please Login or Register  to view this content.

In F2 I added a field header called "Hide Criteria" and I inserted a narrow row between the end of the table and the summary section area.

I then selected cells F2:F80 and added an autofilter (so a single filter). I then hid column F so it wouldn't be an eyesore.

I then amended the VBA code in the worksheet's class module to this:

Please Login or Register  to view this content.

Let us know if that suits you?

EDIT: Amended formula.

Hi John,

I could not get the HideRows macro you suggested at the top of the thread to work with your new macro, so I created a macro called HideRowsAwards. Is it possible to incorporate your suggestion in the old macro?

This suggestion entirely replaces the initial one.

Also I have a second Awards sheet in the workbook with less columns (see code below) and I modified to suit this but this is another macro HideRowsAwards1. How would I combine these two into one macro in the Module and then make Range adjustments in the Worksheet macro?

Please would you attach the latest file and I (or someone else) will combine them together for you. If you protect the sheets manually and allow filtering, you won't need to unprotect/protect the sheets in your code.

Hi John,

I think this is what you want?

Hi John,

The only issue is that the Autofilter Field should be set to 6 for the Awards sheet and 5 for the AwardsPO sheet. Autofilter Field:=1 does not work. Resetting the Autofilter this will show this. Does that mean I have to create two macros?

If you need to reset the filter then, when you reapply it, do it only for the helper column like I did. That way it will always be field 1. You could change the sub to accept an additional argument which specifies the field number but I don't really see the need.

Also noticed that now I have #NUM! in the empty rows. Is there anyway to change that?

Which empty rows are showing errors? When I opened your attachment I got errors in Awards-PO!A82:91 because they link to another workbook.

Hi Colin,

OK I got it. I applied the autofilter to all columns rather than applying it to the helper column alone. I am learning ! Thank you!

Also the #NUM! disappears after I entered a number, so no problems there.

Really appreciate your help! I will close this thread.

John

Pls start your own thread as per forum rules.