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

Answers to Excel - Is there a posibility to offset an array of ranges? ( 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
    

    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

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

◀ Go back