Excel VBA Macro: Autofilter All Values In A List (Dynamic Range)
HTML-код
- Опубликовано: 19 ноя 2019
- Excel VBA Macro: Autofilter All Values In A List (Dynamic Range). In this video, we create a macro that filters data based on a list of values. With Excel VBA, you can use the code we write in this video to automatically filter data pertaining to all values in a dynamic list. The list can change in size and the macro will still run without having to alter the code, for list lengths greater than 1. The code determines the length of the list and then uses a combination of transpose, join and split functions to arrange the list in an array that meets the compatibility criteria for autofilter.
Data used in this video:
gsociology.icaap.org/datauplo...
#ExcelVBA #ExcelMacro
Autofilter Values Not in a List 🤓😎 : ruclips.net/video/en6Tb7qrxGw/видео.html
Today only I needed it to apply my project. Very useful information
Thank you so much, this is super helpful.
I think your tutorials are great! is there any way to use wildcards in the list?
Also once I have it filtered I wanted to add language to another cell. Do you use offset to get there. I used to use the count / last row and loop but i feel like there has to be a better way. Thanks!!!! (for example if you filtered by a list of car models in column D and once you did the filter you wanted to enter "car" in column B (if its blank).
It works. Great, thanks! But in my Excel it also works without the join and split steps. Only difference seems you are using a 1 or a 0 based array. It eventually gives the same filtering.
can you share the code?
@@ultrahen2755 Sure I can. But it is the same as shown here in the video. I only noticed that you can skip 2 of these transformations.
@@karinbeyaert9950 my list no show or zero, i dont know why, already type the code from the video
This is great work. :)
Thanks!!
You are the GOAT
Appreciate you 💪💪
Thanks a lot brother...What if i want to copy the data to a new worksheet within the same WorkbooK?
This works great! Is there a quick way to modify the coding so it exclude the values in the dynamic range?
Thanks! Not exactly quick. There's no direct way to exclude more than 2 values in a list, but I created a workaround here: ruclips.net/video/9EyPIExWH8Q/видео.html
Works good
Nice!
Heii! :) Thanks for the great explanation - i was able to implement that! :)
However, I do have an issue and i really really hope there is a quick and easy fix. I don't want an exact match of the criterias, I need a "contains" match. i tried to do
Criteria1:=list & "*"
but that didnt work. I tried an asterix at the beginning and end of my criteria in the original criteria list, but that also didn't work.
I would be super grateful, if you could help me!
Excellent tutorial! But I want to the opposite of including values in the list. I have a list of values, each list value appears as a part of cell values across multiple row. I would like to filter these out (hence the rest of the rows represents the data I want to see without losing the filtered out data). Can you please do another video for that? I have multple columns I want to filter using this method, and I guess it'll need to be in separate macros. Also, to retain the ability to Auto Save, I think I can save these macros to my Perosnal workbook?
Thanks! Hope this helps: ruclips.net/video/en6Tb7qrxGw/видео.html and yes, you should be able to save them locally
Thank you this helped me a lot! Is there a way to filter out based on values that are NOT in the list ?
I tried adding ""& list to the criteria1 but that gave me an error
Glad to hear that! Sorry for the late reply! You won't be able to do that directly if there's more than 2 , but this video shows how to do 3 or more : ruclips.net/video/9EyPIExWH8Q/видео.html hope that helps! To incorporate a list you would just combine the concepts of both videos together. Lmk if you need further guidance. I'll respond faster next time! 😅
I am from India. Thanks for this video. It help on my Projects. But I need you help. How we can do the same think "Not equal", since I tried with "" on criteria but it's showing Type mismatch error.
How about if we put our list starting to B2, what would be code?
how can I add wildcards in each of the criteria cell in the list
Thank you for this - does this method work in a table?
Also - if your data has a comma in it, will that make difference i.e "Surname, Firstname"? The filter doesn't find my list and returns blanks.
@@TheMightyid yes, but you would have to stack the columns on the table into a single column for this code to work. And yes, it will work if the data has a comma in it as long as the spacing/text length matches. Hope that helps!
This is very applicable if the list contains one criteria only. How if there's a list in sheet 1 contains "Oceana, Western Europe" (for example)? Because I've tried mine and it doesn't work by this vba join and split function. it will show "Oceana" only. The list "Oceana, Western Europe" will not show up.
You might have to Trim them after you Split to remove extra blank characters. They have to match exactly, an extra space will mess it up
@@greggowaffles I've already asked a lot of forum about this. One of them is on stackoverflow. And they said it's impossible. The only way to do this is by hiding rows that don't contain those words on cell. Hmm I think I gave you another idea for your content on youtube, maybe you can make one *lol. Thanks for your answer anyway, I really appreciate it :)
Thanks for tutorial 😀. However I'm using product codes (i.e., numbers) for my list and when I run the code I keep getting a Runtime 1004 error. "Autofilter method of Range class failed". Do you know what is causing this error? Thanks
thanks! sorry for the late reply. did you ever get your code to work?
@@greggowaffles Thanks for responding. No I didn't. Any idea what could be the problem?
@@greggowaffles I found your video very helpful as well but experiencing the same error as @gradyelliott3858.. any idea how to fix this ?
Need this answer please.