Excel VBA Macro: Autofilter Based on Cell Value (Step-by-Step Tutorial)

[@greggowaffles]

Autofilter Based on Multiple Cell Values! 😎😎 kzbin.info/www/bejne/f4bLZXlvaNuterM

[@wollebooy]

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?

[@praiserofthesun3645]

5 years later and you saved my ass just because I actually forgot how to do this bs, thanks a lot man

[@greggowaffles]

Anytime! 😎

[@thomasrusli1934]

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

[@lanceross1035]

Helpful video. I didn't realize at first why Field was 2, but figure it out. You present well.

[@greggowaffles]

Appreciate that!

[@palamentor7008]

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!!!!

[@greggowaffles]

No prob! Glad to hear that!!

[@averybrothers1785]

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.

[@naomifagan2885]

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

[@Dexter101x]

More or less of what I wanted to be honest, but is there a way to clear the filter after running this?

[@darpananand1302]

Can you also explain how to auto filter using cell value in different workbook

[@darpananand1302]

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.

[@bobbygoud5518]

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

[@greggowaffles]

Hope this helps! Excel VBA Macro: Autofilter All Values In A List (Dynamic Range) kzbin.info/www/bejne/gmqlmX5secqdrpY

[@Drew-vp]

Hi what if you have a cell with defined name. How can you reference that ?

[@ptoricoluv]

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 ?

[@greggowaffles]

Thanks! Yeah, definitely

[@greggowaffles]

Do you have a specific example? I can make a video on it

[@jayganesh1079]

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?

[@alejandroesquivelcordero6970]

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.?

[@samuelborrero-maldonado6353]

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!

[@greggowaffles]

What’s your code look like?

[@josebatoctoy9248]

How to filter multiple columns using cell value and can still function even if other values to be filtered is empty?

[@simo758]

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...?

[@greggowaffles]

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

[@prathaprgowda8481]

What to do when i have to filter by 2 values (2 or multiple cells values)

[@swethakrishnak6093]

Hi, how to autofilter date range, for example from 07/07/2022 to 25/08/2022

[@dhirajp4677]

It was a nice informative video Gregg..what will be the code if I want to run macro on ENTER key..?

[@greggowaffles]

thanks! Application.OnKey "{ENTER}"

[@RajeshwarUma]

Thanks so much but I have one question it's supported to 2013 version

[@greggowaffles]

no problem! yes it's supported.

[@mofa1266]

Please tell how to remove same filters using macro

[@brianfrank6968]

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)

[@greggowaffles]

Is there any data above Row4 at all?

[@cindymesia401]

sir, what if im gonna search multiple criteria but in keywords is that possible? heres my code that im trying to make: 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

[@raulspro]

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]

No problem! Hope this helps: kzbin.info/www/bejne/gmqlmX5secqdrpY

[@greyhound2743]

This is great - what if the filter value is in another file? Cheers!

[@greggowaffles]

thanks! you can just reference the file name before "Sheets". apologies for the late reply

[@anushiyakp1587]

How to filter the date based on cell value?

[@RJthesoundstreaming]

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.

[@greggowaffles]

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!

[@RJthesoundstreaming]

Yesssss it helps. Thanks you so much for your reply and assistance. You're amazing.

[@greggowaffles]

@@RJthesoundstreaming no prob!

[@RJthesoundstreaming]

@@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...

[@girishankar09]

@@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

[@pankajnegi8792]

Thankyou so much. This is what I was looking for.

[@greggowaffles]

No problem! I'm glad to hear that!!

[@frankpartida8685]

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.

[@greggowaffles]

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

[@LipFung]

@@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?

[@greggowaffles]

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

[@LipFung]

@@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 :)

[@greggowaffles]

No problem!

[@Mzert805]

Amazing video!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[@greggowaffles]

Thanks!!!

[@luisaguilar5758]

Can you do the same with pivot tables?

[@greggowaffles]

yup, check this out: www.thespreadsheetguru.com/blog/2014/9/27/vba-guide-excel-pivot-tables

[@shanetravel]

thank you sir!

[@greggowaffles]

No prob!

[@9985235346]

Hi.. I need assistance on screen clipping VBA. Is it possible?

[@greggowaffles]

Hi Naresh. Yes! You can use VBA and APIs to do it

[@liza-mariwright379]

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

[@greggowaffles]

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"

[@liza-mariwright379]

@@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]

Try cell(1,37).text instead. As long as both column and cell you reference have the same date format, it will work

[@liza-mariwright379]

@@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. ☺

[@liza-mariwright379]

@@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

[@gerardvaneggermond2067]

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.

[@eduardovallejocanales4714]

What about just the background color?

[@greggowaffles]

yeah, check out this video: kzbin.info/www/bejne/b3bcgXx7rbx7brM

[@jameskabah2315]

How to lock cells and used filter in excel vba

[@greggowaffles]

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

[@ZubairChoudhary]

Great

[@greggowaffles]

Thanks!

[@llIIllIlIIllX_XIillIIllIIllIll]

this only works with cells that has characters , it doesn't work with numbers , why???

[@Denis-c9j]

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..

[@llIIllIlIIllX_XIillIIllIIllIll]

@@Denis-c9j hi what worked for me was changeing the formate of the whole column to "String"

[@RenewYourPower]

What if the autofilter doesn't meet with the criteria or field?

[@greggowaffles]

that data wont get filtered. if you get an error, you can use On Error Resume Next to prevent the code from bugging out

[@icezen87]

how to make it dynamic? - when filtering numbers

[@greggowaffles]

Do you have an example?

[@melchorfigueroa1416]

How would you filter for all values in a list?

[@greggowaffles]

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?

[@greggowaffles]

Here you go! :-) kzbin.info/www/bejne/gmqlmX5secqdrpY

[@janjancasao4509]

How about when the column has blank?

[@greggowaffles]

check this out: stackoverflow.com/questions/31519877/filtering-by-blanks-in-vba

[@mmike27angel]

Great!!! Thanks Gregg you really helped me so much, +1Suscriptor

[@greggowaffles]

Thanks! So glad to hear that!!

[@currentpeace]

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!

[@greggowaffles]

Hi! Thanks! You can try using this video: kzbin.info/www/bejne/d6qaYZd8bLRmjas

[@currentpeace]

@@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

[@gopikoorikkattil6498]

How to make the serial number in order after apply filter?

[@fksoftware]

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.