VBA: Filter Data with Multiple Values
HTML-код
- Опубликовано: 13 сен 2018
- #xlFilterVaules #VBA #FilterWithMultipleValues
In this video you will learn how we can use an excel range as a filter criteria and paste the filtered data on another worksheet.
Please download this excel dashboard from below given link:
www.pk-anexcelexpert.com/vba-...
Purchase our Excel Products:
www.pk-anexcelexpert.com/prod...
Visit to learn more:
Chart and Visualizations: www.pk-anexcelexpert.com/cate...
VBA Course: www.pk-anexcelexpert.com/vba/
Download useful Templates: www.pk-anexcelexpert.com/cate...
Dashboards: www.pk-anexcelexpert.com/exce...
Watch the best info-graphics and dynamic charts from below link:
• Dynamic Graphs
Learn and free download best excel Dashboard template:
• Excel Dashboards
Learn Step by Step VBA:
• VBA Tutorial
Website:
www.PK-AnExcelExpert.com
Facebook:
/ pkan-excel-expert-9748...
Telegram:
t.me/joinchat/AAAAAE2OnviiEk5...
Twitter:
/ priyendra_kumar
Pinterest:
/ pkanexcelexpert
Send me your queries on telegram:
@PKanExcelExpert
this men deserve a very special place on haven, you make a great work thanks
Thanks for your valuable feedback
Just what I needed. Thanks PK! I thought autofilter syntax, the ARRAY was for the Excel formula Array rather than the vba array data structure type. That cleared it up.
Thanks for your valuable feedback
Great job. I always appreciate your videos. Thank you for sharing your knowledge with us.
Thanks for your valuable feedback
Thank you I was searching the whole web for this!
Thanks for your valuable feedback
PK thanks a lot, speed is perfect not too slow not too fast, easily explained functions. :)
Thanks for your valuable feedback
Dear PK,
Excellent presentation
Really helped
Thank you very much
Expecting many more videos like this...
Thanks for your valuable feedback
Its amazing, greetings from mexico
Thanks for your valuable feedback
You are an excellent teacher, SIR
Thanks for your valuable feedback
Just what i needed, thank you Sir !
Welcome
Thanks a lot! You have no idea how this video has helped me! Thank you, you're a crack!!
Glad it helped!
Ended my search on filtering here. Well explained brother, keep going
Thanks for your valuable feedback
Thanks for your video.
How about filtering data based on selected cells across multiple columns?
Fantastic Work done by you
Thanks for your valuable feedback
Hello, your video is very straight to the point and I will use most of the coding. However, i need some help around the Filter table section. I do not want to "hard code" it. I want to Copy a value from one cell on another sheet and paste it on the Auto Filter criteria. Any thoughts or links you may have?
Thanks for this video
This VBA script worked great. Thank you for the video.
Question, if you wanted to invert the filtered data so that you copy/paste the data that isn't listed in your Filter_Criteria_Sh, how would you go about doing that?
It would be very beneficial to be able to exclude things I want to copy with a list.
AWESOME PK!!!!!!!!!!!!!! You are my HERO!!!!!!
Thanks for your valuable feedback
Thank you for your kind help.
Thanks for your valuable feedback
Very good PK..pls also will do with the dynamic list box or drop down..
Hi! Thanks for very useful video. I am interested how can I adjust macro to copy filtred range to output without headings (first row)? Could you please advise?
Thanks a lot...
It Helped me a lot.
Thanks for your valuable feedback
Just wow .bro..thank you
Most welcome
Thanks for sharing with us !!
I have a question related to same autofilter that what we can do if I need data except these multiple criterias?
Can we use criteria as =''''& array(''EMP-1'', ''EMP-2'', ''EMP-3'')
Thanks PK. Can we have a variable filter say values beginning with J, K and S in VBA. I am able to do it for 2 criteria but not 3.
Great job....
Thanks Durgesh for your valuable feedback
Hi Sir, Thanks for your video's Can you please make a video to run multiple vba macros(more than 4) in one workbook.
Excellent.. Thank you.. Please make more of it on vba pivot table
Thanks for your valuable feedback
Thank you❤
You're welcome 😊
Hi PK, Excellent video, I have a doubt, I want pass input at a tie from multiple fields of Filter
_Criteria sheet to data sheet to filter . And the filtered data I want to paste in to Output sheet.Could you please tell me how to do that
Thanks 😊☺️
Welcome 😊
Great PK
Thanks for your valuable feedback
That's great 👍
Thanks for your valuable feedback
Nice video sir
Thanks for your valuable feedback
Nice tutorial, Thank You. Can you create a tutorial with decisions tree in VBA?
Thanks for your valuable feedback. I will definitely try to make such video very soon
Thank you for sharing very useful macro, but we are not able to understand how the dynamic array stored in "Emp_List()". Can you please explain it logically
again well done !
Thanks for your valuable feedback
@@PKAnExcelExpert Emp_list(i) = Filter_Criteria_Sh.Range("A" & i + 2)
can criteria change to contain the string ?
Very nice video👍
Thanks for your valuable feedback
Or can we have more than two criterias while applying filter "beginning with"... If that clears my question. Any help is appreciated.
Hey - thanks for the tutorial - I have an issue where I need to filter out multiple criteria - is there a way that I can use the Array for to - for your example filter out... EMP1, EMP2, EMP3...? Thanks David
Same Question, would like to filter all items NOT in the group.
Hi, if you have answer for
Filter all items not in the group?
Send to my email ksudhakar2k11@gmail.com
hello, thank you , i need filtred by years in output exemple: 2018-2019-2020
Hi.... Can you please also tell how to defilter more than 2 criteria using VBA....
Hi, How can i find the row number of filtered the data ? I want to edit some of filtered cells.
I have a question if emp name (let it is emp-x) in criteria is not listed in data sheet. Then, I want it will skip and continue to look next criteria. what coding I can use for this???
Thank you PK. I always enjoy watching your videos but wouldn’t using an advanced filter be easier?
Thanks for your valuable feedback. Actually I wanted to tell the use
of xl filtervalues. I will create another video with using advanced filter
how can I get the first field in a column after the filter?
Hi sir good evening
I have one Excel worksheet where the data is more than 5 lakes. In that I have to use filter in three columns like
1) User ID,
2) Transect Originator
3) User name
Copy that Data open New folder new workbook paste,save close.
I had made one VBA code which one taking lot of time
I need to filter using data from activecell.. for example if I click a particular cell in sheet 1 that value should be filtered in sheet 2
Got how to do that? Have that same issue!!!
Thank you. How to restrict only selected column to be copied in Output sheet. I need only few columns in the output. how vba query should be adjusted
Hello, can we transfer your work in this video directly to the listbox instead of the page, thank you.
Please need your help.. I added ur macros.. But it gives only values with numbers, is there any way to make it get data, numbers text or whatever
How to filter based on header name as we are giving autofilter field may be 2 or 3 column numb instead i want to give autofilter field as name ( header name) of the column
Can u pls help me its a real problem i stuck with as my input data's header kept shuffling often
Thanks!
I have only one problem:
I have a table where there are empty cells and the filter is hiding all the rows which don't contain the complete data.
Example:
In a table with headers "Date, Description, Category, Amount, Notes", when i search "Food" the filter doens't show the rows in which the "Notes" cell is empty.
How can I make the filter also show me rows with some empty cells?
Ok it's very interesting but I need data sheet another column random values of employees records only that s records only one one copy could help me
Hi, I am unable to filter using criteria *value using vba. Please share your thoughts am using Excel 2013
Thanks PK. I tried the same code. However, ReDim is not working. Run-time error 9. Subscript out of range. Could you please help on this.
how to let VBA allow to return a normal range if the criteria is empty? I'm trying to implement such a system for an entire database. If I don't want to put a criteria under one field. I still want it to return the ones I have inputted cells to. Any help is much appreciated
instead of all the columns,I want to copy only few columns data from Data tab to output tab, is that possible ?
How about the criteria being in the same cell?
It works, but it only return 2 values on output when i search for emp1 and emp2.. if i add the 3rd emp3 it clears the output sheet. Any idea wc part of code to fix? Tnx.
I am searching many times but could not find any relevant videos....now i just wanted to know that you have given criteria only for 3 rows but how we can do it by putting many line items in criteria...will criteria automatically select ? or we need to change the criteria every time ?
Can you please help here, I am not able to copy filter data in output. All data is getting pasted in output sheet. I am using same code
Hi, M getting error of autofilter method of range class failed. Pl suggest
What is Emp_List as per Excel?
sir, what if i filter multiple criteria but its just a keyword not the exact word? how would I do that?
At a time
How to filter numbers
If one of criteria is not available then it will fail ?
Hi Sir,
Me aapka Subscriber Hu muje aapke saare videos ache lge muje aapse ek help chahiye thi
Aapki is video me hai viase hi muje krna hai bat thodi dikkat aa rahi hai . Muje har ek value ex. Emp ko filetr kar iski kuch values copy krni hai har emp ki alag alg range hogi ex. Kisi emp ki fist 2 to kisi ki 5 aise sabki alag alg par meri define ki huvi range ki value chahiye or ye code har ek emp me run krvana hai
Sir please help and solve this
Your Subscriber,
Samir Kagda
Option Explicit
Sub Filter_Criteria()
Dim Data_sh As Worksheet
Dim Filter_Criteria_Sh As Worksheet
Dim Output_sh As Worksheet
Set Data_sh = ThisWorkbook.Sheets("Data")
Set Filter_Criteria_Sh = ThisWorkbook.Sheets("Filter_Criteria")
Set Output_sh = ThisWorkbook.Sheets("Output")
Output_sh.UsedRange.Clear
Data_sh.AutoFilterMode = False
Dim Emp_list() As String
Dim n As Integer
n = Application.WorksheetFunction.CountA(Filter_Criteria_Sh.Range("A:A")) - 2
ReDim Emp_list(n) As String
Dim i As Integer
For i = 0 To n
Emp_list(i) = Filter_Criteria_Sh.Range("A" & i + 2)
Next i
Data_sh.UsedRange.AutoFilter 2, Emp_list(), xlFilterValues
Data_sh.UsedRange.Copy Filter_Criteria_Sh.Range("c1")
Data_sh.AutoFilterMode = False
End Sub
how criteria is contain , not equal?
Put "*" before and after criteria word
@@PKAnExcelExpert please show to me where put "*" in syntax.
@@PKAnExcelExpert please explain sir