## Excel - Is there a posibility to offset an array of ranges?

Question

Basically I have defined

``````mydata = {\$E\$2:\$H\$6,\$J\$2:\$M\$6,\$O\$2:\$S\$6,\$V\$2:\$Y\$6,\$AA\$2:\$AE\$6,\$AG\$2:\$AJ\$6,\$AM\$2:\$AP\$6, \$AR\$2:\$AV\$6,\$AX\$2:\$BA\$6,\$BD\$2:\$BG\$6,\$BI\$2:\$BM\$6,\$BO\$2:\$BR\$6}
``````

this array of ranges.

Is there a way to offset(mydata,x,y) ?

Context: The reason why I ask is that I have to define protected ranged in the review -> allow users to edit ranges section. And for some files they are even 80 ranges to define. So I thought I would just define the 1st manually and then just offset the rest 50 – 80 ranges.

But offset doesn't support non - adjacent cells so is there a workaround this limitation? :/

Show source

## Answers to Excel - Is there a posibility to offset an array of ranges? ( 1 )

1. Instead of `Array`, you can define your range as a `Union Range` (aka `Multi-Area Range`) so you can easily offset it.

``````Sub Test
Dim multiAreaRange As Range
Set multiAreaRange = Range("\$E\$2:\$H\$6,\$J\$2:\$M\$6,\$O\$2:\$S\$6,\$V\$2:\$Y\$6,\$AA\$2:\$AE\$6,\$AG\$2:\$AJ\$6,\$AM\$2:\$AP\$6, \$AR\$2:\$AV\$6,\$AX\$2:\$BA\$6,\$BD\$2:\$BG\$6,\$BI\$2:\$BM\$6,\$BO\$2:\$BR\$6")