Is it possible to have 42 ranges in an array to loop through them with (for each cell in ...)

[rouzacct]

Sub CALC2_()

    Dim R As Range
    Dim Rng As Range

    Set Rng = ThisWorkbook.Sheets("INV").Range("L2:L1000,P2:P1000,T2:T1000,X2:X1000,AB2:AB1000,AF2:AF1000,AJ2:AJ1000,AN2:AN1000,AR2:AR1000,AV2:AV1000,AZ2:AZ1000,BD2:BD1000,BH2:BH1000,BL2:BL1000,BP2:BP1000,BT2:BT1000,BX2:BX1000,CB2:CB1000,CF2:CF1000,CJ2:CJ1000,CN2:CN1000,CR2:CR1000,CV2:CV1000,CZ2:CZ1000,DD2:DD1000,DH2:DH1000,DL2:DL1000,DP2:DP1000,DT2:DT1000,DX2:DX1000,EB2:EB1000,EF2:EF1000,EJ2:EJ1000,EN2:EN1000,ER2:ER1000,EV2:EV1000,EZ2:EZ1000,FD2:FD1000,FH2:FH1000,FL2:FL1000,FP2:FP1000,FT2:FT1000")
       

For Each cell In Rng
' do something

The code above gives an error 1004, it seems to surpass the limit of such range array

BUT if i take most of the ranges off it works

Sub CALC2_()
   
 Dim R As Range
    Dim Rng As Range

 Set Rng = ThisWorkbook.Sheets("INV").Range("L2:L1000,P2:P1000,T2:T1000,X2:X1000,AB2:AB1000,AF2:AF1000,AJ2:AJ1000,AN2:AN1000,AR2:AR1000,AV2:AV1000,AZ2:AZ1000")
       
For Each cell In Rng
'Do something

The code above works.

What can I do at this point ?
I need to include all 42 columns in one range so I can loop through and Eventually add in a
Do while (range is not empty) to break the loop as soon at it hits a range that is empty.

 

[Rick Rothstein]

Does this work?

    With ThisWorkbook.Sheets("INV")
        Set Rng = Intersect(.Range("L2:L1000").EntireRow, .Range("L:L,P:P,T:T,X:X,AB:AB,AF:AF,AJ:AJ,AN:AN,AR:AR,AV:AV,AZ:AZ,BD:BD,BH:BH,BL:BL,BP:BP,BT:BT,BX:BX,CB:CB,CF:CF,CJ:CJ,CN:CN,CR:CR,CV:CV,CZ:CZ,DD:DD,DH:DH,DL:DL,DP:DP,DT:DT,DX:DX,EB:EB,EF:EF,EJ:EJ,EN:EN,ER:ER,EV:EV,EZ:EZ,FD:FD,FH:FH,FL:FL,FP:FP,FT:FT"))
    End With

Norie, I think you forgot the EntireRow property (see red text above).