Drop Down List for table with non-repeating values

Hello all

I have searched a lot of Google and could not find what I am looking for. It might be possible that I have not used the best words/phrases to search. If so, link me to the thread that contains the same problem as I have. I apologize for that in advance, in case it is true.

Here is the issue.
I have a table where the values for a given column repeat only after a certain number of rows. For eg, I have a field called Priority and it will say have only two values - HIGH and LOW. The values are not repeating with each row and hence a normal drop down will not work.

Take a look at the attachment and let me know a possible solution to achieve this. I would prefer excel formula if possible but VBA solution is fine too. The only thing I cannot do is to create a duplicate sheet or hidden columns with repeated values. All suggestions are welcome though.

Please help!

Hi gajendra.fo,
maybe so

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address(0, 0) <> "C2" Then Exit Sub
If Me.FilterMode Then Me.ShowAllData
If Target.Value = "All" Then Exit Sub
Range("A5:I" & Cells(Rows.Count, 2).End(xlUp).Row).AutoFilter Field:=2, Criteria1:=Target.Value
End Sub

Thanks Nilem for your response.

Please note that values HIGH and LOW cannot be repeated even for code or formula. The value selected in cell C2 should show all the rows corresponding to the selection. Each value for HIGH or LOW has 32 rows associated to it.

Take a look at the file attached! It is only showing the rows where the value exists if I remove the repeated values which is not what I want.

But thanks for the code, it is very helpful.

I think I understand now.
Try again

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address(0, 0) <> "C2" Then Exit Sub
Dim r As Range, rHide As Range
Set rHide = Range("A1")
With Range("B5").CurrentRegion.Columns(2)
    .EntireRow.Hidden = False
    If Target.Value = "All" Then Exit Sub
    For Each r In .SpecialCells(4).Areas
        If r(0, 1) <> Target.Value Then
            Set rHide = Union(rHide, r, r(0, 1))
        End If
    Next r
    If rHide.Count > 1 Then Intersect(rHide, Columns(2)).EntireRow.Hidden = True
End With
End Sub

Perfect!

Thanks a ton!