Excel - Is there a possibility 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? :/

Answer 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")

    Debug.Print multiAreaRange.Offset(3, 4).Address
End Sub

Output:

$I$5:$L$9,$N$5:$Q$9,$S$5:$W$9,$Z$5:$AC$9,$AE$5:$AI$9,$AK$5:$AN$9,$AQ$5:$AT$9,$AV$5:$AZ$9,$BB$5:$BE$9,$BH$5:$BK$9,$BM$5:$BQ$9,$BS$5:$BV$9