Macro to filter showing upper levels

[thc57e]

Hello folks!

I am creating an action list, organised by clusters, with 3 different levels - level 1 (1), level 2 (1.1) and level 3 (1.1.1) -

I would like to filter the user (assigned only to actions in level 3) in a way that it will also show me above the level 2 and level 1 corresponding to it, so it is easier to track it.

I will try to illustrate it. This is the original list:

ID	Level	User
1	1
1.0.1	3	User A
1.1	2
1.1.1	3	User B
1.1.2	3	User C
1.1.3	3	User A
2	1
2.1	2
2.1.1	3	User B
2.1.2	3	User A
2.2	2
2.2.1	3	User B
2.2.2	3	User A
2.2.3	3	User C
3	1
3.0.1	3	User B
3.0.2	3	User A

<colgroup><col><col><col></colgroup><tbody>
</tbody>

and this should be the result if I filter by USER A:

ID	Level	User
1	1
1.0.1	3	User A
1.1	2
1.1.3	3	User A
2	1
2.1	2
2.1.2	3	User A
2.2	2
2.2.2	3	User A
3	1
3.0.2	3	User A

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Thank you very much in advance.

 

[MickG]

Try this:-
Load code into sheet module.
To run code and Filter by Selected value, double click any value in column "C".
To Un-filter , right click anywhere in column "A".

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_BeforeDoubleClick(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, Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]If[/COLOR] Target.Column = 3 And Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
        [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Not Dn.Value = "" [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] Not .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                .Add Dn.Value, Dn
            [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
            [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
[COLOR="Navy"]If[/COLOR] .exists(Target.Value) [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Not K = Target.Value [COLOR="Navy"]Then[/COLOR]
    .Item(K).EntireRow.Hidden = True
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_BeforeRightClick(ByVal Target [COLOR="Navy"]As[/COLOR] Range, Cancel [COLOR="Navy"]As[/COLOR] Boolean)
[COLOR="Navy"]If[/COLOR] Target.Column = 1 And Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
    Cancel = True
    ActiveSheet.Rows.Hidden = False
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[thc57e]

Try this:-
Load code into sheet module.
To run code and Filter by Selected value, double click any value in column "C".
To Un-filter , right click anywhere in column "A".

Private [COLOR=Navy]Sub[/COLOR] Worksheet_BeforeDoubleClick(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, Temp [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] K [COLOR=Navy]As[/COLOR] Variant, c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]If[/COLOR] Target.Column = 3 And Target.Count = 1 [COLOR=Navy]Then[/COLOR]
    [COLOR=Navy]Set[/COLOR] Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
        [COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
    [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
        [COLOR=Navy]If[/COLOR] Not Dn.Value = "" [COLOR=Navy]Then[/COLOR]
            [COLOR=Navy]If[/COLOR] Not .exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
                .Add Dn.Value, Dn
            [COLOR=Navy]Else[/COLOR]
                [COLOR=Navy]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
            [COLOR=Navy]End[/COLOR] If
    [COLOR=Navy]End[/COLOR] If
    [COLOR=Navy]Next[/COLOR]
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] K [COLOR=Navy]In[/COLOR] .keys
[COLOR=Navy]If[/COLOR] .exists(Target.Value) [COLOR=Navy]Then[/COLOR]
    [COLOR=Navy]If[/COLOR] Not K = Target.Value [COLOR=Navy]Then[/COLOR]
    .Item(K).EntireRow.Hidden = True
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] K
[COLOR=Navy]End[/COLOR] With
[COLOR=Navy]End[/COLOR] If
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Private [COLOR=Navy]Sub[/COLOR] Worksheet_BeforeRightClick(ByVal Target [COLOR=Navy]As[/COLOR] Range, Cancel [COLOR=Navy]As[/COLOR] Boolean)
[COLOR=Navy]If[/COLOR] Target.Column = 1 And Target.Count = 1 [COLOR=Navy]Then[/COLOR]
    Cancel = True
    ActiveSheet.Rows.Hidden = False
[COLOR=Navy]End[/COLOR] If
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Hello Mick,

Thank you very very much for your idea. It is almost working. However, it still have an issue.

When I press user C, look what it does:

ID	Level	User
1	1
1.1	2
1.1.2	3	User C
2	1
2.1	2
2.2	2
2.2.3	3	User C
3	1

<colgroup><col><col><col></colgroup><tbody>
</tbody>

It should not show ID 2.1. It should be like this:

ID	Level	User
1	1
1.1	2
1.1.2	3	User C
2	1
2.2	2
2.2.3	3	User C
3	1

<tbody>
</tbody>

Thank you.

 

[MickG]

I'm not too sure what you want !!
Would it be in this particular case (same for all cases) of "User C", looking at cells above that cell up to the next "User C" value for the first blank cells That have "Level" 2 and or "1" in column "Level" and including these cells in the range to be Visible.
That is looking up for the first values up of "1" and "2", and if there is a value include with visible range for "User C".