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.)

Sub HideUnSelectedCells()
    Dim CellsLeft As Range, CellsRight As Range
    Dim CellsAbove As Range, CellsBelow As Range
    
    Dim RangeToSee As Range
    Dim AreaToSee As Range
    Dim rng As Range
    
    Set RangeToSee = Selection
    
    With RangeToSee
        Set AreaToSee = Range(.Areas(1), .Areas(.Areas.Count))
    End With
    
    With AreaToSee
        With .Parent.Cells
            .EntireColumn.Hidden = False
            .EntireRow.Hidden = False
        End With
        On Error Resume Next
            Range(.Parent.Cells(Rows.Count, Columns.Count), .Offset(.Rows.Count, 0).Cells(1, 1)).EntireRow.Hidden = True
            Range(.Parent.Cells(1, 1), .Cells(1, 1).Offset(-1, 0)).EntireRow.Hidden = True
            Range(.Parent.Cells(Rows.Count, Columns.Count), .Offset(0, .Columns.Count).Cells(1, 1)).EntireColumn.Hidden = True
            Range(.Parent.Cells(1, 1), .Cells(1, 1).Offset(0, -1)).EntireColumn.Hidden = True
        On Error GoTo 0
    End With
    
    For Each rng In AreaToSee.Columns
        rng.EntireColumn.Hidden = (Application.Intersect(rng.EntireColumn, RangeToSee) Is Nothing)
    Next rng
    For Each rng In AreaToSee.Rows
        rng.EntireRow.Hidden = (Application.Intersect(rng.EntireRow, RangeToSee) Is Nothing)
    Next rng
End Sub

Sub UnHideAll()
    With Cells
        .EntireColumn.Hidden = False
        .EntireRow.Hidden = False
    End With
End Sub

@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.

' in normal module

Sub ToggleVisiblity()
    With ThisWorkbook
        .OnlyReservedCellsVisible = Not (.OnlyReservedCellsVisible)
    End With
End Sub

Sub ResetTogglingCells()
    Set ThisWorkbook.ReservedCells = Selection
    ThisWorkbook.OnlyReservedCellsVisible = True
End Sub

and put this code in the ThisWorkbook code module

' in ThisWorkbook code module

Dim pReservedCells As Collection

Private Sub Workbook_Open()
    Dim Sh As Worksheet
    Set ReservedCells = New Collection
    For Each Sh In ThisWorkbook.Worksheets
        ReservedCells.Add Item:=Nothing, Key:=Sh.CodeName
    Next Sh
End Sub

Property Get ReservedCells(Optional Sh As Variant) As Range
    If pReservedCells Is Nothing Then Set pReservedCells = New Collection
    If IsMissing(Sh) Then Set Sh = ActiveSheet
    On Error GoTo MakeEntry
        Set ReservedCells = pReservedCells(Sh.CodeName)
    On Error GoTo 0
    Exit Property
MakeEntry:
    pReservedCells.Add Item:=Nothing, Key:=Sh.CodeName
    Resume
End Property

Property Set ReservedCells(Optional Sh As Variant, inVal As Range)
    If pReservedCells Is Nothing Then Set pReservedCells = New Collection
    If IsMissing(Sh) Then
        If inVal Is Nothing Then
            Set Sh = ActiveSheet
        Else
            Set Sh = inVal.Parent
        End If
    End If
    On Error Resume Next
    pReservedCells.Remove Sh.CodeName
    pReservedCells.Add Item:=inVal, Key:=Sh.CodeName
    On Error GoTo 0
End Property

Property Get OnlyReservedCellsVisible(Optional Sh As Variant) As Boolean
    If IsMissing(Sh) Then Set Sh = ActiveSheet
    Dim ReservedArea As Range, SeenCells As Range
    On Error Resume Next
    With ReservedCells(Sh)
        Set ReservedArea = Range(.Areas(1), .Areas(.Areas.Count))
    End With
    On Error GoTo 0
    If ReservedArea Is Nothing Then
        OnlyReservedCellsVisible = False
    Else
        Set SeenCells = Sh.Cells.SpecialCells(xlCellTypeVisible)
        OnlyReservedCellsVisible = (Application.Intersect(SeenCells, ReservedArea).Address = SeenCells.Address)
    End If
End Property

Property Let OnlyReservedCellsVisible(Optional Sh As Variant, inVal As Boolean)
    Dim RangeToSee As Range, AreaToSee As Range, rng As Range
    If IsMissing(Sh) Then Set Sh = ActiveSheet
    Set RangeToSee = Me.ReservedCells(Sh)
    If RangeToSee Is Nothing Then inVal = False
    
    If inVal Then
        With RangeToSee
            Set AreaToSee = Range(.Areas(1), .Areas(.Areas.Count))
        End With
        
        With AreaToSee
            With .Parent.Cells
                .EntireColumn.Hidden = False
                .EntireRow.Hidden = False
            End With
            On Error Resume Next
                Range(.Parent.Cells(Rows.Count, Columns.Count), .Offset(.Rows.Count, 0).Cells(1, 1)).EntireRow.Hidden = True
                Range(.Parent.Cells(1, 1), .Cells(1, 1).Offset(-1, 0)).EntireRow.Hidden = True
                Range(.Parent.Cells(Rows.Count, Columns.Count), .Offset(0, .Columns.Count).Cells(1, 1)).EntireColumn.Hidden = True
                Range(.Parent.Cells(1, 1), .Cells(1, 1).Offset(0, -1)).EntireColumn.Hidden = True
            On Error GoTo 0
        End With
        
        For Each rng In AreaToSee.Columns
            rng.EntireColumn.Hidden = (Application.Intersect(rng.EntireColumn, RangeToSee) Is Nothing)
        Next rng
        For Each rng In AreaToSee.Rows
            rng.EntireRow.Hidden = (Application.Intersect(rng.EntireRow, RangeToSee) Is Nothing)
        Next rng
    Else
        With Sh.Cells
            .EntireColumn.Hidden = False
            .EntireRow.Hidden = False
        End With
    End If
End Property

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

If any cells are hidden Then
- remember which cells are hidden
- show all cells
Else
- hide the remembered cells
End

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.

Sub ToggleMyView()
    Dim AllVisible As Boolean
    AllVisible = (Cells.SpecialCells(xlCellTypeVisible).Address = Cells.Address)
    If AllVisible Then
        On Error Resume Next
        ThisWorkbook.CustomViews("myView").Show
        On Error GoTo 0
    Else
        ThisWorkbook.CustomViews.Add viewname:="myView"
        Cells.EntireColumn.Hidden = False
        Cells.EntireRow.Hidden = False
    End If
End Sub

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"?

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!

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

Sub AntiView()
    Dim AllVisible As Boolean
    Dim VisibleRows As Range, oneRow As Range
    AllVisible = (Cells.SpecialCells(xlCellTypeVisible).Address = Cells.Address)
    If AllVisible Then
       Rem no hidden rows to reverse
       Beep
    Else
        Set VisibleRows = Cells.SpecialCells(xlCellTypeVisible).EntireRow
        Cells.EntireRow.Hidden = False
        For Each oneRow In VisibleRows.Areas
            oneRow.EntireRow.Hidden = True
        Next oneRow
    End If
End Sub

This is a modification that limits the action to the UsedRange

Sub AntiView()
    Dim AllVisible As Boolean
    Dim VisibleRows As Range, oneRow As Range
    AllVisible = (Cells.SpecialCells(xlCellTypeVisible).Address = Cells.Address)
    If AllVisible Then
       Rem no hidden rows to reverse
       Beep
    Else
        Set oneRow = Range(Cells(1, 1), ActiveSheet.UsedRange)
        Set VisibleRows = Application.Intersect(Cells.SpecialCells(xlCellTypeVisible), oneRow).EntireRow
        Cells.EntireRow.Hidden = False
        For Each oneRow In VisibleRows.Areas
            oneRow.EntireRow.Hidden = True
        Next oneRow
    End If
End Sub

Duplicate. Deleted.