VBA: Filter Data with Multiple Values

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, thank you , i need filtred by years in output exemple: 2018-2019-2020

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?

how criteria is contain , not equal?

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.

Thanks a lot! You have no idea how this video has helped me! Thank you, you're a crack!!

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

sir, what if i filter multiple criteria but its just a keyword not the exact word? how would I do that?

Thank you❤

Just wow .bro..thank you

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.

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

Hi, M getting error of autofilter method of range class failed. Pl suggest

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

That's great 👍

PK thanks a lot, speed is perfect not too slow not too fast, easily explained functions. :)

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

Fantastic Work done by you

Very nice video👍

Excellent.. Thank you.. Please make more of it on vba pivot table

I have write

Option Explicit

Sub Filter_Cult()
Dim sht2 As Worksheet
Dim Sht1 As Worksheet

Set sht2 = ThisWorkbook.Sheets("Sheet2")
Set Sht1 = ThisWorkbook.Sheets("Sheet1")



sht2.AutoFilterMode = False
sht2.UsedRange.AutoFilter 1, Criteria1:=Sht1.Range("B1")
sht2.UsedRange.AutoFilter 7, Criteria2:=Sht1.Range("B2")
End Sub


but i have want

if Criteria1:=Sht1.Range("B1") B1="ALL"
sht2.UsedRange.AutoFilter 1, SHOW ALL DATA

if Criteria2:=Sht1.Range("B2") B2="ALL"
sht2.UsedRange.AutoFilter 7, SHOW ALL DATA

Ended my search on filtering here. Well explained brother, keep going

again well done !

AWESOME PK!!!!!!!!!!!!!! You are my HERO!!!!!!

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?

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 ?

Hi, I am unable to filter using criteria *value using vba. Please share your thoughts am using Excel 2013

Thanks a lot...
It Helped me a lot.

If one of criteria is not available then it will fail ?

Just what i needed, thank you Sir !

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.

Thanks 😊☺️

Hi, How can i find the row number of filtered the data ? I want to edit some of filtered cells.

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

how can I get the first field in a column after the filter?

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

At a time

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

How to filter numbers

Thank you for your kind help.

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.

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

Dear PK,
Excellent presentation
Really helped
Thank you very much

Expecting many more videos like this...

Thank you I was searching the whole web for this!

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

this men deserve a very special place on haven, you make a great work thanks

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?

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?