#1
|
|||
|
|||
VBA to keep hidden rows hidden when filtering
Please refer to the simple attached workbook. I'm using list boxes in the "Scorecard" sheet to select bundles of Goal Tasks in the "Tasks" sheet. For this uploaded example I've simplified these bundled options into "Not Pursuing" or "Pursuing - Show All Tasks" for two Goals (1 and 2). In the "Tasks" sheet I need to be able to sort (filter) the revealed (not hidden) rows while keeping all hidden rows hidden. The latter part is my problem.
The purpose of this tool is to identify goals to be pursued, which task bundles are to be followed to fulfill them, and to identify who is to perform each task (per row). Each task row will then have labor hours assigned to it and by sorting/filtering we can isolate the total hours for Sally, Dick or Jane. (Hint: I'm over 50.....) If you go directly to the "Tasks" sheet (without adjusting the options selected in the "Scorecard" sheet) and "Un-Select All" in the filter and then select "Sally", you can see that hours for her are revealed in both the pursued Goal (with tasks revealed) as well as the not pursued goal (which prior to sorting/filtering had all task rows hidden.) Can you provide a simple solution to keep the hidden rows hidden (and not include any labor hours, should they happen to be included in the Labor Hours column of a hidden row(s)? Thanks for your help! David |
#2
|
|||
|
|||
Hi David
Hm, you only set the filter to column B. I'm not sure if I understood everything, but as far as I see, you would be much better off applying the filter to all columns, and prefilter column A according to your selection in "Scorecard", instead of just hiding rows. For example it can easily be done to apply the filter to showing for all persons Goals and Tasks to Goal1. That way, you then can do further filtering in "Tasks" according to names without worrying that hidden rows by the autofilter showing up. From that you can even go a step further filling in the third column, and if necessary again filtering for "non-empty"... You know what I mean? To keep initially "manually" hidden rows hidden, won't allow you the use of the autofilter directly, but you will have to do all further "filtering" by vba, or via additional columns. But the setup will be quite a difficult one. |
Tags |
filtering, hidden rows, sorting |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
how to view hidden rows | gsrikanth | Excel | 10 | 01-27-2012 05:29 AM |
hidden rows | gsrikanth | Excel | 5 | 01-19-2012 02:40 PM |
Hidden tables? | dluhop | Word Tables | 1 | 09-04-2011 02:41 AM |
Hidden style applied over already-hidden text. | christie | Word | 1 | 08-17-2011 09:10 AM |
Hidden address | nicolapiva | Outlook | 2 | 11-16-2010 12:12 AM |