Auto Filtering a Table of Data by Typing a Filter Value Outside the Table

[AJKim94]

Hi,

I have a table of numerical and textual data. I would like to filter the table by the number values in column F7:F3000.

I am aiming to type any number, i.e. 0.30, in cell F2 and filter column F7:F3000 for all values greater than or equal to 0.30. It is important that cell F2 is dynamic, i.e. i can type any number into it and get a filter for all values greater than or equal to the input, and I can also delete the number inside of it and the table will reset.

I have experience recording macros and interpreting code but my ability to write code is limited.

Thank you,
AJ

 

[NeonRedSharpie]

Private Sub Worksheet_Change(ByVal Target As Range)    
    If Target.Column = 6 And Target.Row = 2 Then
        ActiveSheet.Range("$A$7:$Z$3000").AutoFilter Field:=6, Criteria1:=">=" & Cells(Target.Row, Target.Column).Value, _
        Operator:=xlAnd
    End If
End Sub

Assumptions made:

Your data goes from A:Z
Your data is already filtered, and will always be filtered
Your data max is static at 3000

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rowStart As Long
    Dim rowEnd As Integer
    
    Dim colStart As Integer
    Dim colEnd As Long
    
    rowStart = 7
    colStart = 1
    With ActiveSheet
        colEnd = .Cells(7, Columns.count).End(xlToLeft).Column
        rowEnd = .Cells(Rows.count, "F").End(xlUp).Row
    End With


    
    
    If Target.Column = 6 And Target.Row = 2 Then
        Range("F7").Select
        Selection.AutoFilter
        ActiveSheet.Range(Cells(rowStart, colStart), Cells(rowEnd, colEnd)).AutoFilter Field:=6, Criteria1:=">=" & Cells(Target.Row, Target.Column).Value, _
        Operator:=xlAnd
    End If
End Sub

Assumptions made...not a lot on this one. Just be sure to change your rowStart and colStart if they're wrong.