[VBA] How to Process Multiple Separated Cell Ranges (Areas) Individually

Introduction

In Excel, you can select multiple separated cell ranges by holding down the Ctrl key.

To process these “multiple areas” using VBA, you can use the Selection.Areas property to handle each area individually.

In this article, I will explain how to process separated cell ranges sequentially using Selection.Areas with practical code examples.

Code Example: Numbering Separate Areas

Dim idx As Long

For idx = 1 To Selection.Areas.Count
    Selection.Areas(idx).Value = "Block " & idx
Next idx

Code Explanation

What is Selection.Areas?

Selection.Areas is a property used to handle currently selected multiple ranges (areas) one by one.

Each area is treated as a Range object, and you can access them using an index, like Selection.Areas(1).

Selection.Areas.Count

This returns the number of currently selected areas.

By using this count in a For loop, you can process each area individually.

Processing Each Area

In the example above, .Value = "Block " & idx assigns a different label to each area based on the loop counter.

Execution Example

For instance, if you have selected the following three cell ranges using the Ctrl key:

1. B2:B4
2. D2:D4
3. F2:F4

Running the code above will input values into each range as follows:

• B2:B4 → “Block 1”
• D2:D4 → “Block 2”
• F2:F4 → “Block 3”

Advanced Application: Applying Different Formatting

You can easily change the color or font style for each area.

Selection.Areas(idx).Interior.Color = RGB(200 + idx * 10, 220, 255)
Selection.Areas(idx).Font.Bold = True

Important Note

• If the Selection is a single continuous range, .Areas.Count will be 1.
• The code works correctly even if only one area is selected, so it is safe to use generally.

Summary

Task	Syntax	Description
Get Multiple Ranges	Selection.Areas	Handles separated ranges.
Get Range Count	.Areas.Count	Returns the number of areas.
Individual Process	.Areas(i)	Processes a specific block.

By utilizing Selection.Areas, you can create flexible macros for tasks such as splitting data, filling in missing entries, or applying area-specific decorations. This technique is highly useful for improving efficiency in daily tasks and automating report processing.