Apr 05 2021 10:16 AM
Hello Everyone,
Happy Easter to all:smiling_face_with_smiling_eyes::smiling_face_with_smiling_eyes:
In a excel report how can we auto filter one columns values by giving min and max value manually whenever we click macro. for example if i need to perform for value >=500 & <=2500 .whenever i click run macro it should ask me the min & max value to enter ,once i choose value after that it should perform other vba codes.
So what code i will write for AutoFilter with multiple criteria?
Like - My criteria is Value is >=500 & <=2500
Please help.
Here is an attached file.
Apr 05 2021 10:43 AM - edited Apr 05 2021 10:44 AM
You may try something like below. The following code will autofilter the column G as in the first line the constant variable called ColumnToFilter is set to 7. Change it as per your requirement.
Const columnToFilter As Long = 7 'This Will filter Column G (Units), change it as required
Public Sub AutoFilter()
Dim wsData As Worksheet
Dim userMin As Long
Dim userMax As Long
Application.ScreenUpdating = False
Set wsData = ThisWorkbook.Worksheets("AutoFilter")
userMin = Application.InputBox("Input the Min Criteria.", "Min Criteria!", Type:=1)
If userMin = 0 Then
MsgBox "You didn't input the Min Criteria. Please try again...", vbExclamation
Exit Sub
End If
userMax = Application.InputBox("Input the Max Criteria.", "Max Criteria!", Type:=1)
If userMax = 0 Then
MsgBox "You didn't input the Max Criteria. Please try again...", vbExclamation
Exit Sub
End If
If wsData.FilterMode Then wsData.ShowAllData
With wsData.Range("A1").CurrentRegion
.AutoFilter Field:=columnToFilter, Criteria1:=">=" & userMin, Operator:=xlAnd, Criteria2:="<=" & userMax
End With
Application.ScreenUpdating = True
End Sub
You may click the button called "Filter Data" on the AutoFilter Sheet to run the code.
Apr 05 2021 11:21 AM
It worked! Thank you so much sir:smiling_face_with_smiling_eyes:
Sir, in attached file, you have written -
Const columnToFilter As Long = 7 (only for G column)
What if we want to multiple columns, so what VBA code i should write?
Here is attached file
Apr 05 2021 11:51 AM
Apr 05 2021 12:01 PM - edited Apr 05 2021 12:10 PM
Const columnToFilter As Long = 7 (only for G column)
This code only for G column.
Suppose i want to autofilter Column F to Column G
So what we write in VBA code??
Please help...???
Here is a attached file.
Apr 05 2021 12:18 PM
Const columnToFilter As Long = 7 (only for G column)
This code only for G column.
Suppose i want to autofilter Column F to Column G
So what we write in VBA code??
Please help...???
Here is the attached file.
Apr 05 2021 12:46 PM
Apr 05 2021 07:25 PM
Apr 05 2021 08:44 PM
You're welcome @Excel!
Please take a minute to accept the post with the proposed solution as a Best Response to mark your question as Solved.
If you have any additional question, you can always open a New Question.