VB to filter

[ccoury09]

I am using the below code in a worksheet, so that when a user double clicks on a cell, the list should be filtered to display those rows with that value. When I do it, the box comes up to tell me the filter has been applied, but the sheet doesn't actually filter.

I know I am missing a line of code, but can't figure out for the life of me. Have been playing for hours, with no success.... Any suggestions would be GREAT!


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
On Error Resume Next
Err.Clear
Selection.AutoFilter Field:=2, Criteria1:=Target.Value
MsgBox Target.Value & " is Filtered"
End Sub

 

[Norie]

Well my first suggestion would be that you aren't actually missing a line of code.

But you do have one that could be obscuring any errors.

ie

On Error Resume Next

 

[njimack]

Instead of Selection.Autofilter.... I think you should be applying this to a specific range.

 

[Andrew Poulsom]

Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    With Range("A1").CurrentRegion
        If Not Application.Intersect(Target, .Cells) Is Nothing Then
            .AutoFilter
            .AutoFilter Field:=Target.Column, Criteria1:=Target.Value
            MsgBox Target.Value & " is Filtered"
            Cancel = True
        End If
    End With
End Sub