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

 

[glfiedler]

Try removing the quotation marks around the criteria1 variable.

 

[My Aswer Is This]

Try this:

Sub Filter_Me_Please()
'Modified  12/12/2019  10:15:58 PM  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
End Sub

 

[amrita17170909]

Thanks so much it works

 

[My Aswer Is This]

Thanks so much it works

Not sure which answer helped you but:

Glad we were able to help you.

Come back here to Mr. Excel next time you need additional assistance.

 

[amrita17170909]

Thanks for your help.

But I am that new to the forum that I dont know how to say which response helped!

The code worked previously but now I am getting the following error " Autofilter method of Range Class Failed"

Any ideas?

 

[My Aswer Is This]

You posted your code and I posted my code.
So I'm not sure what code your now saying is creating a error.

 

[amrita17170909]

The below code is not working. I have slightly tweaked it.

Sub AutoFilter_range()
'Modified 12/12/2019 10:15:58 PM 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:=6, Criteria1:=">" & ans


End Sub

 

[My Aswer Is This]

Why did you tweak my code?
If I provide code you need why do you feal it needs to be tweaked

The script want's to search Column F

You changed 1 to 6

 

[amrita17170909]

I had to tweak the code because the filter has to be applied on the value column of the sheet which is column 6 and not column 1 as per my original code.

If I use auto filter 1 the code will work but it is not the output I am looking for . The filter over 10 million has to be applied on column 6 to get the correct output.