Data Filter

[sambit]

Dear Sir,
I have a data from Range E5: H11. I want to filter Invoice No as per E2 Cell. If I delete Invoice No from E2 cell then data became original Form.

Please find the example file for your reference.

 

[Nebu]

May be this?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False

With Sheet1
   
    On Error Resume Next
    .ShowAllData
    i& = .Cells(Rows.Count, 1).End(xlUp).Row
    If Not Intersect(ActiveCell, .Range("A2:A2")) Is Nothing Then
        If .[A2] <> vbNullString Then
            .Range("A4:H" & i).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
            .Range("A1:A2"), Unique:=False
        ElseIf .[A2] = vbNullString Then .ShowAllData
        End If

    End If

End With

Application.ScreenUpdating = True
End Sub

 

[sambit]

Nebu Sir,
Thanks for your reply. I don't want to choose Invoice No in combo box (Cell "A2"). I'll type manually Invoice No in the above cell i.e, A2

 

[Nebu]

Hi:

Just remove the data validation , the macro will still work.

Thanks

 

[sambit]

Nebu Sir,
After removing the data validation, it does not work.

Example-1 file is attached for your kind information.

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[sambit]

Nebu Sir,
Thanks a lot. Really Excellent.