Excel VBA Range Areas - unclear on the concept

User avatar
Goshute
3StarLounger
Posts: 397
Joined: 24 Jan 2010, 19:43
Location: Salt Lake City, Utah, USA

Excel VBA Range Areas - unclear on the concept

Post by Goshute »

I've never been clear on this - when addressing a multiple area range in Excel

Set myRng = Range ("A1:A20,C1:C20, E1:E20")

I understand that when I'm looping through each cell I don't need to reference the Areas, such as

For each rngArea In myRng.Areas
For each rngCell in rngArea
<do something>

I can just say

For each rngCell In myRng
<do something>

So under what circumstances must I reference the Areas?

(Jan Karel may recall the genesis of this question.)
Goshute
I float in liquid gardens

User avatar
HansV
Administrator
Posts: 78774
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel VBA Range Areas - unclear on the concept

Post by HansV »

In most situations, you don't need to use Areas.

One of the things you can't do with a multi-area range in one go is copy and paste. If you wanted to copy such a range, you'd have to loop through the areas and copy/paste each of them individually.
Best wishes,
Hans

User avatar
rory
5StarLounger
Posts: 818
Joined: 24 Jan 2010, 15:56

Re: Excel VBA Range Areas - unclear on the concept

Post by rory »

If you want to loop through the rows or columns, you need to use areas. Also if you want to use the Cells(row, col) notation, you need to loop through the areas or you will end up referring to cells that are not in the original range.
Regards,
Rory

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 656
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Excel VBA Range Areas - unclear on the concept

Post by Jan Karel Pieterse »

It was never clear to me when you have to step through the areas and when not. To be on the safe side, I usually do use the areas.
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com