Скачать или смотреть How to Use Excel VBA to Filter for Multiple Case-Independent "Contains" Criteria

Learn how to efficiently filter Excel data using VBA for case-insensitive "contains" criteria. This step-by-step guide helps you easily filter for specific text values in your worksheet.
---
This video is based on the question https://stackoverflow.com/q/75601100/ asked by the user 'Repairer' ( https://stackoverflow.com/u/10450858/ ) and on the answer https://stackoverflow.com/a/75602641/ provided by the user 'FaneDuru' ( https://stackoverflow.com/u/2233308/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Excel VBA filter for multiple case independent "Contains" criteria

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Excel VBA Filtering for Multiple Case-Independent "Contains" Criteria

When working with Excel, especially in data analysis or reporting, you might encounter the need to filter text values based on specific criteria. A common scenario is needing to filter a column for values containing certain substrings. But what if you want this filtering to be case insensitive? Furthermore, if you have multiple criteria, how do you go about implementing this in Excel VBA? Let’s explore how to tackle this problem effectively.

The Challenge

Imagine you have a column in Excel (Column E) filled with text values, and you need to filter this column for any entries that contain any of the substrings "ALL", "SUPER", or "EXTRA". The catch is that you want this filtering to ignore the case of the text, meaning it should recognize variations like "Fall", "fAll", and "FaLL" as valid matches for "ALL".

The Solution

You can achieve this using a VBA macro that processes your data efficiently in-memory and applies a filter based on your specified criteria. Below is a step-by-step breakdown of the code needed to accomplish this task.

Step 1: Set Up Your Environment

Make sure that the worksheet you're working with already has headers in the first row. This ensures that your filters will work effectively.

Step 2: Use the Following VBA Code

The provided macro below will filter Column E based on the specified case-insensitive criteria. Here's how it works:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: How the Code Works

Setting Up Variables: The code declares necessary variables to work with the worksheet, the range for your criteria, and the last row and column of your dataset.

Creating Criteria Array: arrCrit holds the substrings you want to search for, making it easy to adjust as needed.

Loading Data into Memory: By loading data into an array, the process becomes faster, especially with larger datasets.

Filtering Logic: The nested loops check each row's text against the criteria. If there's a match (case insensitive), it marks it.

Applying the Filter: Finally, the code applies an AutoFilter based on the markers you've generated.

Conclusion

Using the VBA code provided, you can easily filter data in Excel for case-insensitive text matches based on multiple criteria. This approach saves time and allows for efficient data manipulation, ensuring only the desired entries are displayed. By adapting the code as per your needs, you can make Excel a powerful ally in handling text data effectively.

Implementing this filtering method will drastically improve your ability to manage and analyze your data, especially when dealing with large datasets.