Hide, Switch to hidden

Hi

I am stuck.

How do I hide all EXCEPT selected (highlighted) rows (or columns).

and then

Toggle view to hidden or visible back and forth ("Microsoft works" used to do this in the old days...)

Thanks

You say it did it in the old days, but your profile suggests you are using an old version: which version are you using?

"Microsoft works" was a "kind-of-office" package back in windows 3.1.

Yes, I am requiring an answer in excel for xp (2002).





edit: Do I have to duplicate this post in the vba section too?

"Microsoft works" was a "kind-of-office" package back in windows 3.1.

Yes, I know - I go back a lot further than that!

As Cytop has said, ask a mod to move the thread to the VBA section if that's the type of solution you are looking for.

As far as I am aware, this is only possible using VBA.

Deleted - no longer relevant.

This routine will hide all unselected cells. If a discontinuous range is selected, it will hide as many cells as excel allows.
(e.g if you select the discontinuous range A1:C10, G12:R20, then it will be visible and G1:R10 will also be visible as will A12:C20.)

Please Login or Register  to view this content.

@mikerickson

Could you please modify to hide all EXCEPT selected cell ROWS?
thx



EDIT: Never mind, all I have to do is to show hidden columns.

BUT: How to switch views back and forth?

This should do what you want.

This is a two step process
1) Define which cells you want to be always visible. Select a range and run the sub ResetTogglingCells.

2) That sub will make only those cells visible. To switch back and forth, run the sub ToggleVisiblity, which it will.

3) If you want a different range of cells to be always visible, run the ResetTogglingCells routine again, after selecting the new always-visible range.

Each sheet will have its own ReservedCells (the ones that are always visible). Toggling the visibility setting on one sheet won't change any other sheet.
The ResetTogglingCells and ToggleCells will only act only on the active sheet.

Put this code in a normal module.

Please Login or Register  to view this content.

and put this code in the ThisWorkbook code module

Please Login or Register  to view this content.

The code that I wrote is for the environment "user designates a range. A routine (ToggleVisibility) switches between only those cells visible and all cells visible. The user hiding/undhiding with normal excel features does not change the designated range."

It sounds like you are looking for a routine that toggles between "show all cells and return-to-previous-hidden status" where user hiding/showing cells does effect the previous hidden status.

It sounds like the logic of this routine would be

Please Login or Register  to view this content.

Is that what you are looking for?
How do you want the user changing sheets to effect this? Will changing to a different sheet wipe the memory of the last hidden cells?

This reminded me of CustomViews.
I don't know where it is located in your version of Excel, but in Excel 2011 its under the View menu.

I think that this will do as I mentioned in the previous post.

Please Login or Register  to view this content.

This macro only shows all rows...

See sample workbook

I opened your workbook.

Manualy hid rows 2,4,6,8,10,12,14,16,18 and 20

Ran ToggleViews, the result was that all rows were visible

I ran ToggleViews again, the result was that Rows 2,4,6,8,10,12,14,16,18 and 20 were again hidden

Ran ToggleViews, all rows were visible

Ran ToggleViews again, Rows 2,4,6,8,10,12,14,16,18 and 20 were hidden

Is this not what you wanted?

No! Please read post 16 again!

Manualy hid rows 2,4,6,8,10,12,14,16,18 and 20 (So, you are left with the odds).

Ran ToggleViews, the result was that all rows were visible. (Desired result: Show the ones you have hidden: even 2-20)

I ran ToggleViews again, the result was that Rows 2,4,6,8,10,12,14,16,18 and 20 were again hidden. (Desired result: Show the odds 1-19)

Ran ToggleViews, all rows were visible. (Desired result: Show the even)

Ran ToggleViews again, Rows 2,4,6,8,10,12,14,16,18 and 20 were hidden. (Desired result: Show the odd)



edit: I prefer to have this macro in the PERSONAL.XLS if I could.

So its not "toggle between all and some" but "toggle between one group and another"


How does one know which are to be omitted.

In the example with 2,4,6,...20 as one group, you want 1,3,5,...,19 to be shown, but how does one know about 21, 22, 23 .....

Is the first group "see 1,3,5,7,9,11,13,15,17,19,21,22 and all higher rows" and the other group "see only 2,4,6,8,10,12,14,16,18 and 20 and no other rows"?

If you are dealing only with whole rows, this might work for you.

Please Login or Register  to view this content.

This is a modification that limits the action to the UsedRange

Please Login or Register  to view this content.

Originally Posted by mikerickson

So its not "toggle between all and some" but "toggle between one group and another"


How does one know which are to be omitted.

In the example with 2,4,6,...20 as one group, you want 1,3,5,...,19 to be shown, but how does one know about 21, 22, 23 .....

Is the first group "see 1,3,5,7,9,11,13,15,17,19,21,22 and all higher rows" and the other group "see only 2,4,6,8,10,12,14,16,18 and 20 and no other rows"?

"but how does one know about 21, 22, 23 ...." : Only 10 rows we manually hid and 21,22,23 (in fact nothing below 20 was hidden), so one view would be odd 1-19 plus 21,22,23... till the end of sheet, and the other view would be even 2-20 (just the ones you manually hid).

"Is the first group "see 1,3,5,7,9,11,13,15,17,19,21,22 and all higher rows" and the other group "see only 2,4,6,8,10,12,14,16,18 and 20 and no other rows"?"
Yes, that is it.

Did not try your next post yet, I 'll let you know.
Thanks!

Duplicate. Deleted.