Alternative to filtering / advanced grouping

[Ronnie12345]

Hi Mr Excel,

I need an alternative to filtering. Something like grouping that will give me a handy button on the lefthandside...

I understand groups. I select three adjacent rows and do shift-alt-rightarrow and a button appears on the lefthandside allowing me to open or close.

But what if the rows I want to appear or disappear are not adjacent? Usually I would just use a filter to make them appear but in this instance that would be too cumbersome.

For example: in the following I would like buttons next to each ticket type so I can show the different years' figures next to each other.

Year Ticket Type Number sold
2019 A 10
2019 B 20
2019 C 30

2018 A 5
2018 B 10
2018 C 15

2017 A 2
2017 B 4
2017 C 6

I need a group-like-button next to 2019 figures so when I press it I get:

Year Ticket Type Number sold
2019 A 10
2018 A 5
2017 A 2

Obviously, I could just filter but there are so many ticket types that option would just be a pita.

Any ideas? Thanks in advance.

Best wishes,

Ronnie

PS - is it possible to insert pictures not from URLs here?

 

[MickG]

Try this Right Click Event:-
To insert code:- Right click SheetTab >>Select "View Code">> Window appears>> Paste code into Vbindow>>Close Vbwindow.

To run code Right Click value in column "B", List filtered, to UnHide Right click in column "A".

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_BeforeRightClick(ByVal Target [COLOR="Navy"]As[/COLOR] Range, Cancel [COLOR="Navy"]As[/COLOR] Boolean)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nRng [COLOR="Navy"]As[/COLOR] Range

[COLOR="Navy"]If[/COLOR] Target.Column = 2 [COLOR="Navy"]Then[/COLOR]
Cancel = True
[COLOR="Navy"]Set[/COLOR] Rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Not Dn.Value = Target.Value [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
    [COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Hidden = True
    [COLOR="Navy"]Set[/COLOR] nRng = Nothing
[COLOR="Navy"]Else[/COLOR]
    Cancel = True
    [COLOR="Navy"]If[/COLOR] Target.Column = 1 [COLOR="Navy"]Then[/COLOR] Cells.EntireRow.Hidden = False
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Kyle123]

Sounds like you want a Pivot table