VBA help in creating a dependent and source views

[himajam]

Hi Excel Experts,
I'm looking for vba code for my below excel data. I couldn't figure out how to do it. Hope someone could help me
I have Product and Categories data (2 columns). For each Product, it should show me its corresponding Categories and on click of category, it should show me corresponding products.

Eg data:
Product Category
P1 C1
P1 C2
P2 C1
P3 C1
P3 C2
P3 C3

Output data:
P1 C1 If I click on C1, it should show me P1,P2,P3
---C2 If I click on C2, it should show me P1,P3

P2 C1 If I click on C1, it should show me P1,P2,P3

P3 C1 If I click on C1, it should show me P1,P2,P3
----C2 If I click on C2, it should show me P1,P3
----C3 If I click on C3, it should show me P1,P2,P3

[p45cal]

There is some built-in functionality for this; right-click on a cell and choose Filter|Filter by selected cell's value.
You could automate it a bit with this in the sheet's code-module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Columns("A:B")) Is Nothing Then
  If (Me.AutoFilterMode And Me.FilterMode) Or Me.FilterMode Then Me.ShowAllData
  Range("A1").CurrentRegion.Resize(, 2).AutoFilter Field:=Target.Column, Criteria1:=Target.Value
End If
End Sub

[himajam]

Hi p45cal (VBAX Guru) , Thanks a lot... It worked like a charm... Kudos to you... You have solved it just like that (minimal code).. Awesome