Formula or Code to keep Hidden Rows Hidden

[Carol]

My spreadsheet has several columns. Two of which are: Phase & Account Name.
Account Name is in Alphabetical Order, and each account has a Phase assigned
to it. (1,2,3,4,5,etc). I am using Auto-Filter to view each phase
seperately.

I have also created a bunch of rows right under the header row that shows
Phase Title, and Target Dates. These rows are hidden. It's nice - because
now when I sort my Phases, they show titles, and dates, and I have columns
that indicate the progress...

Problem: When I change the auto-filter drop-down from an actual phase back
to "all" - it unhides the rows, and makes my spreadsheet look ridiculous.

Is there any type of formula or code that can keep these rows hidden when
"All" is selected in Auto-Filter?

 

[Elkar]

One option:

You could add a new column and then re-apply your Auto-Filter to include
this new column. Place an "X" in this new column for any row that you want
to have visible. Then, set the filter for this column to show only "X"s.
That way, when you select "show all" for your Phase Column, it will still
only show the "X" rows.

HTH,
Elkar

 

[Carol]

Tried it - but it didn't work: I have 6 "title rows". To get them to show
using your solution, I have to add the "x" to each of those rows as well.
So, when I select "show all" in the phase column, it once again, shows all
the title rows. Dang - I thought that might work.

 

[Elkar]

Hmm... I'm afraid I don't understand exactly what you're trying to do then.
How are your "Title Rows" identified in the Phase column? Does each Phase
have it's own Title Row? It may help if you could provide an example of what
your data looks like (Title Row compared to Data Row).

 

[Carol]

Below is a snapshot of my sheet. Rows 1 thru' 3 are hidden. When I use
Auto-Filter, for, say Phase 1 (column A) - Row 1 appears as a "title" and
holds the actual "due" dates for each launch. The dates in rows 4-8 can
then be changed to indicate the date the work will be ready for launch...
I'll never lose site of the actual required phase dates - because of these
"title" rows. My sheet, starting at row 4 is inalphabetical order by Account
Name. During the course of this project, accounts will move in and out of
phases - it's easier to just change the number in the Phase column, than
actually move the whole row.
Does this help? (Thank you for trying!)
A B C
Phase A Launch B Launch Account
Row1(Hdn) 1 6/18/07 7/23/07 Phase1 Launch Dates
Row2(Hdn) 2 6/23/07 8/06/07 Phase 2 Launch Dates
Row3(Hdn) 3 7/23/07 9/04/07 Phase 3 Launch Dates
Row4 6 8/23/07 9/23/07 ABC Corp
Row5 1 5/23/07 7/23/07 DEF Corp
Row6 1 6/04/07 7/23/07 GHI Corp
Row7 2 Launched 8/06/07 LMN Corp
Row8 1 4/30/07 7/23/07 XYZ Corp

 

[Elkar]

Ok, that helps, I think I see what you're getting at now. The only non-VB
option I can think of is basically a two-step process, following my original
idea.

Keep the additional column with the "X"s. Put "X"s in all data rows,
leaving title rows blank. Then, whenever you filter by a phase, set the X
filter to show "all". When you set the phase column to "all", set the X
filter to "X". So, whenever you turn one on, turn the other one off.

Now, I'm not an expert with VB, but here's another possiblity. Add this
code to your worksheet. Press ALT-F11, then select the appropriate
worksheet. Paste this code into the worksheet window:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A2").Parent.AutoFilter.Filters(1).On = False Then
Rows("2:4").Hidden = True
End If
End Sub


Note: adjust the Rows("2:4") to match the row numbers of your Title Rows.

Now, this will accomplish what you want automatically, without needing the
"X" column, but when you switch to "all" on the Phase filter the code won't
run until you select a new cell. So, again, it'll be a two-step process
(select "all", then click on any cell).

Someone in the Excel.Programming group may be able to offer a better VB
solution, if these still aren't quite what you want.

HTH,
Elkar

 

[Carol]

Hi again Elkar - YES! That will work. I'd love it if we didn't have to click
a cell to re-hide those title rows, but this definitly serves the purpose!
Thank you so much for sticking with me on this one!