Thanks man, I've been working so hard to figure this out, but now with this video and a little bit of customizing, I'm able to have my macro work. Really appreciate it!!!!
Hello, how about filter based on specific value in the cell, for the example the value is "Amer", so the data will be show as Northern America, Latin Amer & Carib, etc
Hello, thanks for the video! Can you explain how to filter on a specific cell that is in another sheet in the same file? I am trying to filter a column in my "report" sheet based on a cell in my "HOME" sheet.
Dude, I am about to break my work computer. I follow everything exactly but for me I get "Expected: End of statement" with the "Field" highlighted. It would greatly help if you had any suggestions on this!! thank you!
Can you do multiple criteria? I want create a coulmn for user to just enter whatever multiple values in criteria and data to be generated automatically
Please share VBA code to filter based on cell value in different workbooks. You explained using from different sheet in same workbook but i need VBA code to filter data from cell value present in different workbook.
How can you get a reference to a cell in the first row of filtered data?. For example, you filter those with an area less than 1000 sq. mi. and THEN you need to click on a button that shows all the elements of the region of the first element of that filtered set (but not only those less than 1000 sq.mi.?
I have made an excel file in which there is macro to hide zero value. If I run the macro, other values (except Zero) will be shown, and then if I again use filter, all the data shows. Any solution?? Any macro for both?
It's putting my filter on Row 1 when my data starts in Row4, why? I have the exact same code as you (it's messing up the format and filtering out the header)
Great video. Can i filter more than a single value? lets say if we have a date for example and i want to filter both date and range. Should i duplicate the Sheet("Sheets).Range etc...?
thanks! yes, you can use the line of code from the video twice in the same sub and just change the AutoFilter field and Criteria, so that one cell you reference has the date and the other cell has the range
Thank you so much. It help a lot to a beginner like me. Can you please tell how can I filter 2 or more cell value. As you searched EASTERN EUROPE . but I want to search EASTERN EUROPE, NORTHERN AFRICA & OCEANIA.
@@greggowaffles And if that criteria date changes based on the cell value how to I make it see the value is the same? Cell(1,37).value is what you say in the video. How do I change that so that it sees it as a date. Thanks for getting back to me so quickly
@@greggowaffles No that also didn't work, it filters out all the rows. It doesn't pick up the date that I provide. Thanks anyway, I will play around a bit, I'm sure I can figure it out. ☺
@@greggowaffles Just wanted to let you know that I managed to get it done with ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=10, Criteria1:=1 _ , Operator:=11, Criteria2:=0, SubField:=0
This vba works really well. Thank you so much for the video. I have one question, is that possible if the cell value can read based on single character or any series of characters (doesn't have to be the same) and use wildcard, for example contains "*" or "?" on the vba code. If possible, how to do so? Is there a way to do that? I need it so much.
Thanks! Yup, if you put N* in the cell from the code in the video you'll see Northern Africa, Near East, and Northern America get filtered. Hope that helps!
@@greggowaffles hello, I think I wanna ask you something. There are 2 sheets in my Excel workbook: Sheet1: MUFG Client Sheet 2: Company Information So basically I want to do autofilter at MUFG Client sheet in "Keyword" field (Field 29) from another cell (I18) in Company Information sheet. And the content of the cell is the result from vlookup formula so it will change and not always be the same. Here goes my vba code: Sub filter_by_cell_value () Sheets("MUFG Client").Range("A2").Autofilter Field:=29, _ Criteria1:="=*" & Sheets("Company Information").Cells(18,6).Value & "*", xlOperator:= xlOr End Sub My objective is I want the autofilter can read the text in cell I18 without specific text/criteria. For example, if the cell I18 contains Cosmetics, Chemical --> I want the autofilter in Keyword Field can show the word Cosmetics or Chemical, then If I change the content of company information sheet into different company (The result of vlookup), the cell I18 in Company Information will change into Food & Beverage, Business Expansion, FMCG--> And I also want the autofilter in the keyword Field (MUFG Client sheet) shows Food & Beverage or Business Expansion or FMCG (Autofiltering contains those words by ignoring order) And from my vba code above, Cells(18,6) is cell I18 in Company Information Sheet. Is it possible to do so? I think I have to discuss this directly to make you understand. Sorry if this makes misunderstanding. Thank you so much...
@@greggowaffles The video was helpful...My use case is where the cell based criteria is dynamic so I cannot put any character like N* or A* but need to use wildcards to cell value. In the video it shows as "Criterial:= Cells(2, 6).Value". So how can I add * to this so that when ever cell value changes and we run macros, it automatically adds * at the beginning or end of the word in the cell
Great videos... I've learned a few things by watching your videos, Thanks... I have a question on this. Can this same auto-filter be done based on cell value, but to look in several columns at once without being limited to one column only. In other words, in your example, to be able to type either country or region in cell value and to filter that?... Thanks in advance.
thanks, Frank! you can use the line of code from the video twice in the same sub and just change the AutoFilter field and Criteria, so that one cell you reference has the country and the other cell has the region. apologies for the late reply
Hey you can try to use an array for that. So you would use Dim arr as Variant and then set arr = cell value. Then use arr as your criteria in your filter
@@greggowaffles Hi, i managed to type the code suggested by you. unfortunately , the result will show nothing when search the keyword. i believe it cannot filter 2 column at the same time. Anyway, thanks for your help :)
you can lock the cells one time manually, and just have your code unlock the cells each time it runs and relock the cells when it ends. apologies for the late reply
Hi Melchor! You can assign your list a name, then set an array called X equal to that list for example. From there, you Transpose X and then Split and Join the contents of X with commas. After that, you can autofilter X like you would any other array. I can make a video of that if you'd like?
Hello sir, maybe you can help me, I made a code but it is too long now I want to reduce it, is this possible? One section has the following code; Application.ScreenUpdating = False If Sheets("Menu").Range("E2") = 1 Then Sheets("toplay1").Select Range("A1").Select5 ActiveCell.Formula2R1C1 = _ "=FILTER('S1'!R[1]C[1]:R[20]C[1],'S1'!R[1]C[1]:R[20]C[1]" "")" Range("B1").Select ActiveCell.Formula2R1C1 = _ "=FILTER('S1'!R[1]C[5]:R[20]C[5],'S1'!R[1]C[5]:R[20]C[5]" "")" This continues, at (c[ ]) each time 4 is added until 37 is reached Could you help me? Thanks in advance.
@@greggowaffles Hello, thank you for your answer, but that still not resolve my issue. Could you please be so kind and show me how to apply this to a particular cell address. Number 1 is ok, but I still receive "cells(2,6).values" as a text I tried to filter out. Thanks
Autofilter Based on Multiple Cell Values! 😎😎 th-cam.com/video/IUh4F93vzDQ/w-d-xo.html
i have tried to follow you video, step by step. When i run the macro, i got a 1004 runtime Error. Can you tell me why i experince this issue?
5 years later and you saved my ass just because I actually forgot how to do this bs, thanks a lot man
Anytime! 😎
Helpful video. I didn't realize at first why Field was 2, but figure it out. You present well.
Appreciate that!
Thanks man, I've been working so hard to figure this out, but now with this video and a little bit of customizing, I'm able to have my macro work. Really appreciate it!!!!
No prob! Glad to hear that!!
Hello, how about filter based on specific value in the cell, for the example the value is "Amer", so the data will be show as Northern America, Latin Amer & Carib, etc
It’s just not working for me at all says subscript out of range and I can’t figure out why the only thing I’ve changed is column numbers
Hello, thanks for the video! Can you explain how to filter on a specific cell that is in another sheet in the same file? I am trying to filter a column in my "report" sheet based on a cell in my "HOME" sheet.
That was great. Easy to follow. Thanks. Is it possible to create a vba to filter a table based on a cell value and another for blanks ?
Thanks! Yeah, definitely
Do you have a specific example? I can make a video on it
Dude, I am about to break my work computer. I follow everything exactly but for me I get "Expected: End of statement" with the "Field" highlighted. It would greatly help if you had any suggestions on this!! thank you!
What’s your code look like?
Can you do multiple criteria? I want create a coulmn for user to just enter whatever multiple values in criteria and data to be generated automatically
Hope this helps! Excel VBA Macro: Autofilter All Values In A List (Dynamic Range)
th-cam.com/video/L9BhK6Dejx4/w-d-xo.html
Can you also explain how to auto filter using cell value in different workbook
Please share VBA code to filter based on cell value in different workbooks.
You explained using from different sheet in same workbook but i need VBA code to filter data from cell value present in different workbook.
Thankyou so much. This is what I was looking for.
No problem! I'm glad to hear that!!
More or less of what I wanted to be honest, but is there a way to clear the filter after running this?
How can you get a reference to a cell in the first row of filtered data?. For example, you filter those with an area less than 1000 sq. mi. and THEN you need to click on a button that shows all the elements of the region of the first element of that filtered set (but not only those less than 1000 sq.mi.?
I have made an excel file in which there is macro to hide zero value. If I run the macro, other values (except Zero) will be shown, and then if I again use filter, all the data shows. Any solution?? Any macro for both?
It's putting my filter on Row 1 when my data starts in Row4, why? I have the exact same code as you (it's messing up the format and filtering out the header)
Is there any data above Row4 at all?
How to filter multiple columns using cell value and can still function even if other values to be filtered is empty?
Thanks so much but I have one question it's supported to 2013 version
no problem! yes it's supported.
Great video. Can i filter more than a single value? lets say if we have a date for example and i want to filter both date and range. Should i duplicate the Sheet("Sheets).Range etc...?
thanks! yes, you can use the line of code from the video twice in the same sub and just change the AutoFilter field and Criteria, so that one cell you reference has the date and the other cell has the range
Hi what if you have a cell with defined name. How can you reference that ?
It was a nice informative video Gregg..what will be the code if I want to run macro on ENTER key..?
thanks! Application.OnKey "{ENTER}"
Amazing video!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Thanks!!!
This is great - what if the filter value is in another file? Cheers!
thanks! you can just reference the file name before "Sheets". apologies for the late reply
Thank you so much. It help a lot to a beginner like me. Can you please tell how can I filter 2 or more cell value. As you searched EASTERN EUROPE . but I want to search EASTERN EUROPE, NORTHERN AFRICA & OCEANIA.
No problem! Hope this helps: th-cam.com/video/L9BhK6Dejx4/w-d-xo.html
this only works with cells that has characters , it doesn't work with numbers , why???
hello, did you understood why does it works like that?
i have the same trouble and would be so thankful if u can help me..
@@Denis-c9j hi
what worked for me was changeing the formate of the whole column to "String"
What to do when i have to filter by 2 values (2 or multiple cells values)
Hallo! I want to filter a date value, how would I phrase the criteria to have this, it doesn't pick it up as a date. Thanks in advance
Hey! You just want to have the criteria in the same format as the date in the column you're filtering. Ex: Criteria1:="7/20/2021"
@@greggowaffles And if that criteria date changes based on the cell value how to I make it see the value is the same? Cell(1,37).value is what you say in the video. How do I change that so that it sees it as a date. Thanks for getting back to me so quickly
Try cell(1,37).text instead. As long as both column and cell you reference have the same date format, it will work
@@greggowaffles No that also didn't work, it filters out all the rows. It doesn't pick up the date that I provide. Thanks anyway, I will play around a bit, I'm sure I can figure it out. ☺
@@greggowaffles Just wanted to let you know that I managed to get it done with ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=10, Criteria1:=1 _
, Operator:=11, Criteria2:=0, SubField:=0
Hi, how to autofilter date range, for example from 07/07/2022 to 25/08/2022
Great!!! Thanks Gregg you really helped me so much, +1Suscriptor
Thanks! So glad to hear that!!
How to make the serial number in order after apply filter?
You can't make any changes to the data set when the filter mode is true unless you copy the filtered data to a different sheet.
This vba works really well. Thank you so much for the video.
I have one question, is that possible if the cell value can read based on single character or any series of characters (doesn't have to be the same) and use wildcard, for example contains "*" or "?" on the vba code. If possible, how to do so? Is there a way to do that? I need it so much.
Thanks! Yup, if you put N* in the cell from the code in the video you'll see Northern Africa, Near East, and Northern America get filtered. Hope that helps!
Yesssss it helps. Thanks you so much for your reply and assistance. You're amazing.
@@RJthesoundstreaming no prob!
@@greggowaffles hello, I think I wanna ask you something.
There are 2 sheets in my Excel workbook:
Sheet1: MUFG Client
Sheet 2: Company Information
So basically I want to do autofilter at MUFG Client sheet in "Keyword" field (Field 29) from another cell (I18) in Company Information sheet. And the content of the cell is the result from vlookup formula so it will change and not always be the same. Here goes my vba code:
Sub filter_by_cell_value ()
Sheets("MUFG Client").Range("A2").Autofilter Field:=29, _
Criteria1:="=*" & Sheets("Company Information").Cells(18,6).Value & "*", xlOperator:= xlOr
End Sub
My objective is I want the autofilter can read the text in cell I18 without specific text/criteria.
For example, if the cell I18 contains Cosmetics, Chemical --> I want the autofilter in Keyword Field can show the word Cosmetics or Chemical, then
If I change the content of company information sheet into different company (The result of vlookup), the cell I18 in Company Information will change into Food & Beverage, Business Expansion, FMCG--> And I also want the autofilter in the keyword Field (MUFG Client sheet) shows Food & Beverage or Business Expansion or FMCG (Autofiltering contains those words by ignoring order)
And from my vba code above, Cells(18,6) is cell I18 in Company Information Sheet.
Is it possible to do so? I think I have to discuss this directly to make you understand. Sorry if this makes misunderstanding.
Thank you so much...
@@greggowaffles The video was helpful...My use case is where the cell based criteria is dynamic so I cannot put any character like N* or A* but need to use wildcards to cell value. In the video it shows as "Criterial:= Cells(2, 6).Value". So how can I add * to this so that when ever cell value changes and we run macros, it automatically adds * at the beginning or end of the word in the cell
thank you sir!
No prob!
Can you do the same with pivot tables?
yup, check this out: www.thespreadsheetguru.com/blog/2014/9/27/vba-guide-excel-pivot-tables
Great videos... I've learned a few things by watching your videos, Thanks... I have a question on this. Can this same auto-filter be done based on cell value, but to look in several columns at once without being limited to one column only. In other words, in your example, to be able to type either country or region in cell value and to filter that?... Thanks in advance.
thanks, Frank! you can use the line of code from the video twice in the same sub and just change the AutoFilter field and Criteria, so that one cell you reference has the country and the other cell has the region. apologies for the late reply
@@greggowaffles i have the same question same with Frank Partida. but is it possible to have 2 criteria in 1 cell reference instead of 2?
Hey you can try to use an array for that. So you would use Dim arr as Variant and then set arr = cell value. Then use arr as your criteria in your filter
@@greggowaffles Hi, i managed to type the code suggested by you. unfortunately , the result will show nothing when search the keyword. i believe it cannot filter 2 column at the same time. Anyway, thanks for your help :)
No problem!
Hi.. I need assistance on screen clipping VBA. Is it possible?
Hi Naresh. Yes! You can use VBA and APIs to do it
Please tell how to remove same filters using macro
How to lock cells and used filter in excel vba
you can lock the cells one time manually, and just have your code unlock the cells each time it runs and relock the cells when it ends. apologies for the late reply
How would you filter for all values in a list?
Hi Melchor! You can assign your list a name, then set an array called X equal to that list for example. From there, you Transpose X and then Split and Join the contents of X with commas. After that, you can autofilter X like you would any other array. I can make a video of that if you'd like?
Here you go! :-) th-cam.com/video/L9BhK6Dejx4/w-d-xo.html
how to make it dynamic? - when filtering numbers
Do you have an example?
What about just the background color?
yeah, check out this video: th-cam.com/video/9EyPIExWH8Q/w-d-xo.html
What if the autofilter doesn't meet with the criteria or field?
that data wont get filtered. if you get an error, you can use On Error Resume Next to prevent the code from bugging out
How about when the column has blank?
check this out: stackoverflow.com/questions/31519877/filtering-by-blanks-in-vba
Hello sir, maybe you can help me, I made a code but it is too long now I want to reduce it, is this possible? One section has the following code;
Application.ScreenUpdating = False
If Sheets("Menu").Range("E2") = 1 Then
Sheets("toplay1").Select
Range("A1").Select5
ActiveCell.Formula2R1C1 = _
"=FILTER('S1'!R[1]C[1]:R[20]C[1],'S1'!R[1]C[1]:R[20]C[1]" "")"
Range("B1").Select
ActiveCell.Formula2R1C1 = _
"=FILTER('S1'!R[1]C[5]:R[20]C[5],'S1'!R[1]C[5]:R[20]C[5]" "")"
This continues, at (c[ ]) each time 4 is added until 37 is reached
Could you help me?
Thanks in advance.
Great
Thanks!
Hello, very nice video but I can't write "cells(2,6).value" it gives me this as a name. Could anybody help? thank you!
Hi! Thanks! You can try using this video: th-cam.com/video/Ay70dF7O3WI/w-d-xo.html
@@greggowaffles Hello, thank you for your answer, but that still not resolve my issue. Could you please be so kind and show me how to apply this to a particular cell address. Number 1 is ok, but I still receive "cells(2,6).values" as a text I tried to filter out. Thanks
How to filter the date based on cell value?