Question related to Autofilter using min and max value selection

%3CLINGO-SUB%20id%3D%22lingo-sub-2254446%22%20slang%3D%22en-US%22%3EQuestion%20related%20to%20Autofilter%20using%20min%20and%20max%20value%20selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2254446%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3EHappy%20Easter%20to%20all%3Asmiling_face_with_smiling_eyes%3A%3Asmiling_face_with_smiling_eyes%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIn%20a%20excel%20report%20how%20can%20we%20auto%20filter%20one%20columns%20values%20by%20giving%20min%20and%20max%20value%20manually%20whenever%20we%20click%20macro.%20for%20example%20if%20i%20need%20to%20perform%20for%20value%20%26gt%3B%3D500%26nbsp%3B%20%26amp%3B%20%26lt%3B%3D2500%20.whenever%20i%20click%20run%20macro%20it%20should%20ask%20me%20the%20min%20%26amp%3B%20max%20value%20to%20enter%20%2Conce%20i%20choose%20value%20after%20that%20it%20should%20perform%20other%20vba%20codes.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ESo%20what%20code%20i%20will%20write%20for%26nbsp%3BAutoFilter%20with%20multiple%20criteria%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ELike%20-%20My%20criteria%20is%26nbsp%3BValue%20is%26nbsp%3B%3CSTRONG%3E%20%26gt%3B%3D500%20%26amp%3B%20%26lt%3B%3D2500%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20an%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2254446%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2254474%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20related%20to%20Autofilter%20using%20min%20and%20max%20value%20selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2254474%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20something%20like%20below.%20The%20following%20code%20will%20autofilter%20the%20column%20G%20as%20in%20the%20first%20line%20the%20constant%20variable%20called%20ColumnToFilter%20is%20set%20to%207.%20Change%20it%20as%20per%20your%20requirement.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EConst%20columnToFilter%20As%20Long%20%3D%207%20%20%20%20'This%20Will%20filter%20Column%20G%20(Units)%2C%20change%20it%20as%20required%0A%0APublic%20Sub%20AutoFilter()%0ADim%20wsData%20%20%20%20%20%20As%20Worksheet%0ADim%20userMin%20%20%20%20%20As%20Long%0ADim%20userMax%20%20%20%20%20As%20Long%0A%0AApplication.ScreenUpdating%20%3D%20False%0A%0ASet%20wsData%20%3D%20ThisWorkbook.Worksheets(%22AutoFilter%22)%0A%0AuserMin%20%3D%20Application.InputBox(%22Input%20the%20Min%20Criteria.%22%2C%20%22Min%20Criteria!%22%2C%20Type%3A%3D1)%0A%0AIf%20userMin%20%3D%200%20Then%0A%20%20%20%20MsgBox%20%22You%20didn't%20input%20the%20Min%20Criteria.%20Please%20try%20again...%22%2C%20vbExclamation%0A%20%20%20%20Exit%20Sub%0AEnd%20If%0A%0AuserMax%20%3D%20Application.InputBox(%22Input%20the%20Max%20Criteria.%22%2C%20%22Max%20Criteria!%22%2C%20Type%3A%3D1)%0A%0AIf%20userMax%20%3D%200%20Then%0A%20%20%20%20MsgBox%20%22You%20didn't%20input%20the%20Max%20Criteria.%20Please%20try%20again...%22%2C%20vbExclamation%0A%20%20%20%20Exit%20Sub%0AEnd%20If%0A%0AIf%20wsData.FilterMode%20Then%20wsData.ShowAllData%0A%0AWith%20wsData.Range(%22A1%22).CurrentRegion%0A%20%20%20%20.AutoFilter%20Field%3A%3DcolumnToFilter%2C%20Criteria1%3A%3D%22%26gt%3B%3D%22%20%26amp%3B%20userMin%2C%20Operator%3A%3DxlAnd%2C%20Criteria2%3A%3D%22%26lt%3B%3D%22%20%26amp%3B%20userMax%0AEnd%20With%0A%0AApplication.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20click%20the%20button%20called%20%22%3CSTRONG%3EFilter%20Data%3C%2FSTRONG%3E%22%20on%20the%20AutoFilter%20Sheet%20to%20run%20the%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2254526%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20related%20to%20Autofilter%20using%20min%20and%20max%20value%20selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2254526%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20worked!%20Thank%20you%20so%20much%20sir%3Asmiling_face_with_smiling_eyes%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESir%2C%20in%20attached%20file%2C%20you%20have%20written%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EConst%20columnToFilter%20As%20Long%20%3D%207%26nbsp%3B%3C%2FSTRONG%3E(only%20for%20G%20column)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20if%20we%20want%20to%20multiple%20columns%2C%20so%20what%20VBA%20code%20i%20should%20write%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20attached%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2254569%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20related%20to%20Autofilter%20using%20min%20and%20max%20value%20selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2254569%22%20slang%3D%22en-US%22%3EWould%20you%20please%20elaborate%20what%20exactly%20you%20are%20trying%20to%20achieve%3F%3CBR%20%2F%3EWhat%20do%20you%20mean%20when%20you%20say%20that%20you%20want%20multiple%20columns%3F%20For%20how%20many%20columns%20you%20would%20like%20to%20ask%20criteria%20from%20user%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2254585%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20related%20to%20Autofilter%20using%20min%20and%20max%20value%20selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2254585%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EConst%20columnToFilter%20As%20Long%20%3D%207%26nbsp%3B%3C%2FSTRONG%3E%3CSPAN%3E(only%20for%20G%20column)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThis%20code%20only%20for%20G%20column.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ESuppose%20i%20want%20to%20autofilter%20%3CSTRONG%3EColumn%20F%20to%20Column%20G%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ESo%20what%20we%20write%20in%20VBA%20code%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help...%3F%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Regular Contributor

Hello Everyone,

Happy Easter to all

 

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.

8 Replies

@Zan_Hanifee 

 

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.

 

 

@Subodh_Tiwari_sktneer 

It worked! Thank you so much sir

 

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

Would you please elaborate what exactly you are trying to achieve?
What do you mean when you say that you want multiple columns? For how many columns you would like to ask criteria from user?

@Subodh_Tiwari_sktneer 

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.

@Zan_Hanifee 

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.

What would be the autofilter criteria for column F (Sales)?
And your Min and Max criteria are for which column? Column G (Units)?
Thank you very much sir
Now answer is coming correcty..
Thank you for helping me

You're welcome @Zan_Hanifee!

 

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.