Скачать или смотреть Resolving Excel VBA Autofilter with String Criteria for Numbers

Learn how to effectively use VBA's Autofilter with numerical criteria in Excel by converting numbers to strings for proper filtering.
---
This video is based on the question https://stackoverflow.com/q/73782194/ asked by the user 'Leedo' ( https://stackoverflow.com/u/15586050/ ) and on the answer https://stackoverflow.com/a/73783150/ provided by the user 'Ike' ( https://stackoverflow.com/u/16578424/ ) 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: Autofilter using (Range as Criteria) works with strings , But not works with numbers (even after number converted to string)

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.
---
Understanding the Excel VBA Autofilter Issue with Numbers

If you've ever worked with Excel and VBA, you might have encountered a common problem when applying the Autofilter feature using range criteria. This issue particularly arises when your criteria contain numbers—essentially, the Autofilter does not function as expected. Although you may convert the numbers into strings, the Autofilter results may still come up empty. Let’s break down the problem and find a suitable solution.

The Problem: Autofilter Doesn't Work with Number Criteria

You may have found that when using the following VBA code, the Autofilter successfully filters text but fails when you expect it to work with numbers. The trouble often lies in how the criteria are being referenced and filtered. The ultimate issue is that the Autofilter expects explicit string values, making it seem like a straightforward numerical comparison might simply not work.

Here's a simplified version of the problematic code you might encounter:

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

As you can see, the AutoFilter is not returning any results despite the steps taken.

The Solution: Converting Numbers to Strings

The key to resolving the issue lies in converting each number in the criteria range to a string. This way, the Autofilter will process them correctly without misinterpreting their values. Follow these steps to implement the solution in your code.

Step-by-Step Code Modification

Declare a Loop Variable: Begin by declaring a variable to loop through your criteria array.

Convert to String: Inside the loop, convert each element to a string using CStr().

Updating the Code: Update your original code with the following block right after transposing the criteria range.

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

Final Revised Code Snippet

Here's how your code will look after the modifications:

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

Conclusion

In summary, when dealing with Excel VBA's Autofilter, numbers should be explicitly converted to strings to ensure proper functionality. The simple addition of a loop to convert numerical values can make a significant difference, allowing your VBA script to run smoothly without error. Next time you encounter this issue, you’ll know exactly how to overcome it!

Feel free to leave questions in the comments below or share your experiences using this method!