Filter Linked to a Cell Value

[Andy Pilkington]

Hi I wonder if anyone can help?

I want to automate filtering by applying a macro after inputting a value in a cell, the value of which I would like the filter to apply.

While I can automate the cell value by using Combo boxes and I can apply a macro of my choice, I do not know how to link a macro to a cell value in the spreadsheet.

Is this possible? I look forward to hearing from you.

Thanks in advance, ANDY PILKINGTON

 

[My Aswer Is This]

Since you gave no specific details.

Here is a example.

When you enter any value in Range("A1")

The script will run filtering column 5 with the value in Range("A1")

When it finds the value in Range("A1") it will delete that row.

So if Range("A1") has the value Cake entered all rows in the Sheet with Cake in column 5 will be deleted.
If it finds none you will get a message saying found none.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
'Modified  9/6/2018  4:20:11 AM  EDT
Application.ScreenUpdating = False
Dim Lastrow As Long
Dim c As Long
Dim s As Variant
c = 5 ' Column Number Modify this to your need
s = Range("A1").Value 'Search Value Modify to your need
Lastrow = Cells(Rows.Count, c).End(xlUp).Row
With ActiveSheet.Cells(1, c).Resize(Lastrow)
    .AutoFilter 1, s
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
End If
Application.ScreenUpdating = True
End Sub