Filter a column for a userinput value

[amrita17170909]

Hi All,

I am trying to build a code which will accept a user input for the filter.

So for example if userinput = 10,000 the code is going to only show the values over 10,000.

I also want the range to be dynamic but I am not sure how to incorporate that

Sub AutoFilter_range()

'Filter to show values over 10 Million
Application.ScreenUpdating = False

'Dim Range_to_filter As Range
'Dim UserInputValue As Long

UserInputValue = InputBox("Please enter value in millions.", , 10)

Set Range_to_filter = Range("F6:f178")

Range_to_filter.AutoFilter Field:=6, Criteria1:="UserInputvalue"

Application.ScreenUpdating = True

End Sub

 

[My Aswer Is This]

So your saying you tried my code as written and it did not work.
Is that what your saying?
The way my code is written it will filter on column 6

 

[amrita17170909]

I tried your code and it doesn't work.

It applies the filter on the first column which is Customer CRN and as all values are under 10 million the output is nil .

 

[My Aswer Is This]

Are you saying its filtering on column A?

Or are we dealing with a Table and its filtering on column 1 of a Table

 

[amrita17170909]

It was not the filter but the range . I am using the code as per below and it worked.

Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "F").End(xlUp).Row
Dim ans As Long
ans = InputBox("Please enter value in millions.", , 10)
ActiveSheet.Range("F4:F" & Lastrow).AutoFilter Field:=1, Criteria1:=">" & ans
End Sub[/CODE]

 

[My Aswer Is This]

It was not the filter but the range . I am using the code as per below and it worked.

Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "F").End(xlUp).Row
Dim ans As Long
ans = InputBox("Please enter value in millions.", , 10)
ActiveSheet.Range("F4:F" & Lastrow).AutoFilter Field:=1, Criteria1:=">" & ans
End Sub[/CODE]

So now your saying the script works. So not sure why 10 minutes ago you said it did not work. So I'm glad you now have it working.

 

[amrita17170909]

Thanks for your help

 

[My Aswer Is This]

Thanks for your help

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[amrita17170909]

I have another post I need help with please have a look to see if you can give some guidance:
Check condition which involves matching filtered tables and copying it in new sheet

 

[glfiedler]

You will want the field to equal 1 because the field value is the offset from your range. Your range is column F. I expect the code provided by "My answer is this" would work.

 

[My Aswer Is This]

You may want to use this modified code in case no values are found that match your criteria.

Sub Filter_Me_Please()
'Modified  12/16/2019  12:23:12 AM  EST
Dim Lastrow As Long
Lastrow = Cells(Rows.count, "F").End(xlUp).Row
Dim ans As Long
ans = InputBox("Please enter value in millions.", , 10)
ActiveSheet.Range("F6:F" & Lastrow).AutoFilter Field:=1, Criteria1:=">" & ans
ans = Range("F6:F" & Lastrow).SpecialCells(xlCellTypeVisible).count
    If ans < 2 Then
        MsgBox "No Values found"
        Range("F6:F" & Lastrow).AutoFilter
    End If
End Sub