Скачать или смотреть Resolving Excel String Variables Not Working in Range

Discover how to troubleshoot and correctly implement string variables in `Excel` VBA ranges for effective row hiding.
---
This video is based on the question https://stackoverflow.com/q/63421518/ asked by the user 'Aaron' ( https://stackoverflow.com/u/9194149/ ) and on the answer https://stackoverflow.com/a/63421790/ provided by the user 'Yair' ( https://stackoverflow.com/u/14078161/ ) 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 string variables not working in range

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.
---
Resolving Excel String Variables Not Working in Range: A Simple Guide

If you're working with Excel and VBA, you might find yourself in a situation where your string variables are not behaving as expected when attempting to hide selected rows. You may wonder why certain combinations of code work, while others throw errors. Let’s break down the problem and explore a reliable solution that will keep your code organized and maintainable.

The Problem: String Variables Not Resolving in Ranges

Imagine you are trying to hide certain rows in your Excel worksheet using a macro, but when you incorporate string variables for your row ranges, the code fails to execute properly.

Here’s the scenario:

Working sample of code:

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

Non-working version:

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

In the non-working version, the problem arises because VBA does not know how to handle the two string variables together as a valid range when separated by a comma.

The Solution: Using Concatenation to Combine Range Strings

Using the Ampersand Operator

To combine both string variables effectively, you can use the ampersand (&) to concatenate them into a single string that VBA will recognize as a valid range. Here's how you can do it:

Revised Code Example

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

Explanation of the Code

Constants Defined: Set1 and Set2 are defined as constants representing rows you want to hide.

Concatenation: The use of Set1 & "," & Set2 creates a single string that looks like 1:2,4:5,7:8,11:12, which is a valid range format recognized by Excel.

Hiding Rows: The method EntireRow.Hidden = True hides the specified rows in the worksheet named "Sheet1".

Conclusion

By using the ampersand operator to concatenate your string variables in Excel VBA, you can effectively manage and hide sets of rows without combining them into one single string upfront. This approach not only adheres to best practices but also keeps your code clean and easier to maintain as it grows.

Now, you can confidently write macros that utilize separate string variables for row ranges and troubleshoot any related issues without hassle!