Group and Ungroup problem - hidden rows

[djhampson]

Hi,

I use a reporting tool which takes data from our financial system and
creates lovely Excel workbooks.

I'm currently witting a report to budget some revenue over the next 12
months with the ability to drill-down from the Cost Centre down to the
clients level. To do this I have used the grouping feature of Excel
which works nicely however when I group and then ungroup a section any
hidden rows that where hidden in the original list are revealed.

An example, if my spreadsheet is setup like this

R1 Cost Centre Name and Total Line
R2 Client 1 Name and Totals
R3 Client 1 codes (HIDDEN)
R4 Client 2 Name and Totals
R5 Client 2 codes (HIDDEN)


When the report runs I get the following
R1 Cost Centre Name and Total Line
R2 Client 1 Name and Totals
R4 Client 2 Name and Totals

If I group up to the Cost Centre level, I get

R1 Cost Centre Name and Total Line

If I then ungroup this, I get

R1 Cost Centre Name and Total Line
R2 Client 1 Name and Totals
R3 Client 1 codes
R4 Client 2 Name and Totals
R5 Client 2 codes

The HIDDEN rows (#3 & 5) should remain hidden but they don't and its
quite annoying as they contain some ugly data that I don't want people
to see.

Does anyone know what can be done to make these rows stay hidden??