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

AJKim94

New Member
Joined
Jul 30, 2014
Messages
2
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

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Code:
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

Code:
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.
 

Forum statistics

Threads
1,085,589
Messages
5,384,624
Members
401,913
Latest member
chethan av

Some videos you may like

This Week's Hot Topics

Top