Autofilter Criteria with Array in Excel VBA

In this video, we are going to learn about AutoFilter with array. By using array in AutoFilter we can take multiple string values to apply filter in a column.
How do you AutoFilter multiple columns in Excel VBA?
How do I create a filter in Excel VBA?
Using string array as criteria in VBA autofilter
VBA filter by Array of strings as criteria
#vba
#excel
#vbamacro
#vbamacros

[@neejudeshwal6515]

Thanks sir

[@jitendrashelar4123]

Great! Thank you for sharing it.

[@tryandlearn27]

Thanks Jitendra

[@ExcelMadeEasy444]

Great Job Sirji.

[@tryandlearn27]

Thanks Ashok

[@shadow_gaming_sk]

Excellent

[@SanviPatil2019]

fantastic

[@tryandlearn27]

Thanks Vikas

[@vishalmulik3647]

Thank you sir

[@aponijuanzosimo9923]

how about using array but the values in array is not coding? It seems your value will get to another cells.

[@starcreation5329]

How to get values from multiple textbox in array

[@tryandlearn27]

Hi Star, To get values from textbox in array for filter
Private Sub CommandButton1_Click()
TextboxValue1 = UserForm1.TextBox1.Value
TextboxValue2 = UserForm1.TextBox2.Value
Thisworkbook.Sheets("Sheet1").Range("A1:E20").AutoFilter field:=1, Criteria1:=Array(TextboxValue1, TextboxValue2), Operator:=xlFilterValues
End Sub

[@lilyfullery4779]

I have one question ‘ if the given the iteam in array , if its not their in filter then will it throw error ??

[@tryandlearn27]

No, while filtering it will not throw any error.

[@LATEST_MOVIES_CLICK]

Very nice & informative video.
6 lac items mein se 5000 search karna hai. Ye array mein nhi aa rha, kaise kre. Plz reply.

[@tryandlearn27]

Thanks dhanuj, can you share the piece of code? So that I can see why it's not working for you.

[@LATEST_MOVIES_CLICK]

@@tryandlearn27
3 methods use kr chuka hu.
Ek next comment mein likhta hu

[@LATEST_MOVIES_CLICK]

@@tryandlearn27
Sub RPT()
Application.ScreenUpdating = False
On Error Resume Next
Dim sourceworkbook As Workbook
Dim currentworkbook As Workbook
Set currentworkbook = Workbooks("Book1")
Set sourceworkbook = Workbooks("Book2").Open
Range("A1").Select
Selection.AutoFilter
Range("D:D").AutoFilter Field:=4, Criteria1:=Application.Transpose(currentworkbook.Worksheets("Sheet1").Range("A2:A6830").Value2), Operator:=xlFilterValues
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Application.DisplayAlerts = False
Windows("Book1").Activate
Sheets.Add
ActiveSheet.Paste
Selection.Columns.AutoFit
ActiveWorkbook.Save
sourceworkbook.Close
Set sourceworkbook = Nothing
Set currentworkbook = Nothing
Application.ScreenUpdating = True
End Sub

[@LATEST_MOVIES_CLICK]

@@tryandlearn27
Actually, I'm auditing 10 months. 6830 policy numbers Jo ki ek column mein hain, har month mein search karna hai. Har month ke policy register(PR) hai jisme 55 columns hai. [10 months mein Crores of policy hai, months ke PR bhi parts mein hai 10 lac se jyada line item hai.]
6830 ke liye vlookup se 55 columns HR mahine aur us mahine k har part, bahut time consuming hoga. Search karke copy paste kr lu vo behtar hai. Advance filter bhi use Kiya hai, but slow hai, or manually Krna pdega, folders mein files par loop use Krna aata hai, vo mein kr lunga.
6830 numbers criteria1 ki array mein kaise aayega?

[@tryandlearn27]

Dhanuj, phala tarika to ye hai, ki aap wo 6830 policy no ko range bana le, aur uske baad aap FOR EACH loop and autofilter ka use karke data dusri sheet pe copy kar le but es me problem aapko ye hogi ki ye bahut slow hoga kyunki aapka data bahut bada hai, isliye aap dusra tarika bhi use kar sakte hai joki hai SQL queries ka, ye bahut fast hoga.
Baki aapko koi bhi issue hai, to aap apna number share kar sakte hai, main aapki Puri help karunga

[@khalidmajeed2886]

sir filter mouse ya koi aur but ye to hard coded haay ham naay to dynamic kerna haaay

[@tryandlearn27]

Hi Khalid, AAP mere previous video check kar sakte hai, jahana main bataya hai ki dynamic kaise karte hai. Please check, aur agar phir bhi issue aata hai to aap muzhe comment kar sakte hai, main aapko pura code de dunga

[@gyanshankar9459]

How to use not equal in array

[@tryandlearn27]

Hi Gyanshanker, we can't use not equal to in array in filter