Скачать или смотреть Mastering Excel VBA: Setting a Non-Continuous Range of Cells with Ease

Discover how to define a non-continuous range in Excel VBA, even when using variables for dynamic row selections. Learn essential techniques and tips!
---
This video is based on the question https://stackoverflow.com/q/77529657/ asked by the user 'Leedo' ( https://stackoverflow.com/u/15586050/ ) and on the answer https://stackoverflow.com/a/77529924/ 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, comments, revision history etc. For example, the original title of the Question was: Set a non-continuous cells as a 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.
---
Mastering Excel VBA: Setting a Non-Continuous Range of Cells with Ease

When working in Excel VBA, you may encounter situations where you need to select non-continuous cells as a range. Perhaps you have data structured in a way that requires selecting separate blocks of cells. Understanding how to do this correctly is crucial for efficient coding. In this guide, we’ll walk through a common problem related to defining a non-continuous range of cells and how to solve it effectively.

The Problem

You want to set a range in Excel VBA that includes non-contiguous cells. For example, selecting cells A2:A5 and D2:D5 at the same time works flawlessly with a simple command:

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

However, when you try to use a variable for the last row in your range, you notice that the output is not what you expected. The code snippet you initially wrote may look like this:

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

This might lead to confusion as it does not select the range correctly. Let's break down why this happens and how to fix it.

The Solution

The key to solving this problem lies in properly formatting the string used to define your range. When constructing the range string, the comma separating multiple ranges must be included as part of the string. Here's how to correctly set the range using a variable for the last row.

Step-by-Step Guide

Define Your Worksheet and Find the Last Row:

First, create a reference to your active worksheet and find the last occupied row in column A.

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

Construct the Range String:

Ensure to include the comma within the same string when specifying multiple ranges. Your code should look like this:

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

Complete Code Example:

Here’s how the complete subroutine would look:

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

Quick Summary:

Always remember to include the comma as part of your range string when selecting non-continuous cells.

Utilize the & operator to concatenate your variable for dynamic row selection.

Test your code by using Select temporarily to confirm that the range is correct before incorporating it into your main logic.

Conclusion

Defining non-contiguous ranges in Excel VBA can be tricky, especially when introducing dynamic elements such as variables. However, by ensuring proper string formatting and understanding how range selection works, you can create efficient and functional VBA scripts with ease. We hope that this guide has clarified how to tackle this common issue. Happy coding!