Скачать или смотреть How to Use VBA to Autofilter Data Based on Multiple Criteria from Another Sheet

Discover how to enhance your `VBA` skills by applying autofilters to your database, using criteria from another sheet in Excel.
---
This video is based on the question https://stackoverflow.com/q/66276025/ asked by the user 'johns90' ( https://stackoverflow.com/u/15136039/ ) and on the answer https://stackoverflow.com/a/66276995/ provided by the user 'Evil Blue Monkey' ( https://stackoverflow.com/u/8780258/ ) 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: VBA code - database Autofilter with cells in orther Sheet

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.
---
Mastering VBA Autofiltering with Multiple Criteria from Another Sheet

When working with Excel and VBA, you might encounter a situation where you need to filter a substantial database based on criteria from cells located in another sheet. This is a common requirement for users who need to analyze data swiftly without manually selecting from dropdowns. This post will guide you through understanding and implementing an efficient VBA code to achieve precisely that.

The Problem

You have a database in one sheet and a list of criteria in another. The challenge is to apply an autofilter in the database sheet using all the criteria from the range of cells in the 'Dashboard' sheet. The initial implementation of the code only allowed for filtering based on a single cell, which is limiting if you want to use multiple criteria.

Initial Code Overview

Here’s the starting code you might have used:

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

This code attempts to filter based on the range B4:B11 in the Dashboard sheet but fails to extract multiple criteria effectively.

The Solution: An Enhanced VBA Code

To enhance the functionality, we will rework the code to effectively use all the criteria from the specified range. Below is the improved code with detailed explanations throughout.

New VBA Code

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

Breakdown of the Key Changes

Use of Array for Criteria: The improved code uses an array StrCriteria() to store all the criteria dynamically. This allows multiple criteria to be set for the filter.

Loop Through Criteria Range: The new code utilizes a loop (For Each RngCell In RngCriteria) to iterate over each cell in the criteria range. If a cell is not empty, it is added to the criteria array.

Dynamic Filtering: Instead of trying to filter just one input, the code can now handle multiple values, and it ensures that empty cells are ignored—keeping the filter accurate.

Improved Code Organization: The structure makes it more readable, maintainable, and easier to debug.

Conclusion

With this VBA enhancement, you can now efficiently filter your database using multiple criteria from another sheet. This approach not only saves time but also increases your Excel productivity. If you’re regularly working with data, mastering such functionalities can significantly improve your workflow. Give it a try, and transform the way you handle data in Excel!