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


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
| excel-vba   | excel   | offset   | named-ranges   2017-01-06 00:01 1 Answers

Answers ( 1 )

  1. 2017-01-06 01:01

    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



◀ Go back