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.

 

[Norie]

Does this work?

    With ThisWorkbook.Sheets("INV")
        Set Rng = Intersect(.Range("L2:L1000"), .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

 

[My Aswer Is This]

Try using this script:

It sets all the non empty cells to interior color of Red.
Change this to what you want. If not then exit script.

Sub Select_Me()
Dim i As Long
Application.ScreenUpdating = False
    For i = 12 To 176 Step 4
    
    For Each c In ThisWorkbook.Sheets("INV").Range(Cells(2, i), Cells(1000, i))
        If c.Value <> "" Then
        c.Interior.ColorIndex = 3
        Else
        Exit Sub
        End If
    Next
    
    Next
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

The error is that the the Range string "L2:L1000,P2:P1000,T2:T1000,..." is too long
You can get around it by breaking it up into smaller strings and combining all the smaller ranges. Like this ...

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

 

[rouzacct]

Sub Select_Me3()
Dim i As Long
Application.ScreenUpdating = False
    For i = 12 To 176 Step 4
    
    For Each c In ThisWorkbook.Sheets("VVV").Range(Cells(2, i), Cells(1000, i))
        If c.Value <> "" Then
        c.Interior.ColorIndex = 3
        Else
        Exit Sub
        End If
    Next
    
    Next
Application.ScreenUpdating = True
End Sub

gives 1004 error


I have office 2007 btw

 

[rouzacct]

I tried this and it only works on L column itself and not on the rest

 

[rouzacct]

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

I tried this and I get an error highlighting the _ underscore saying its not a good character

 

[rouzacct]

I like this idea of writing it this way with

[COLOR=#333333]   For i = 12 To 176 Step 4 

[/COLOR]

but copying and pasting your code to a new module i get error 1004
Highlighting

For Each c In ThisWorkbook.Sheets("VVV").Range(Cells(2, i), Cells(1000, i))

I am using 2007 sorry i should have wrote it in my 1st post

 

[My Aswer Is This]

Try this:
I left out one little thing. Set sheet name to what you want:

Sub Select_Me()
'Modified 8-9-17
Dim i As Long
Dim c As Range
Application.ScreenUpdating = False
    For i = 12 To 176 Step 4
    
    For Each c In ThisWorkbook.Sheets("[COLOR=#ff0000]INV[/COLOR]").Range(Cells(2, i), Cells(1000, i))
        If c.Value <> "" Then
        c.Interior.ColorIndex = 3
        Else
        Exit Sub
        End If
    Next
    
    Next
Application.ScreenUpdating = True
End Sub

 

[rouzacct]

Try this:
I left out one little thing. Set sheet name to what you want:

Sub Select_Me()
'Modified 8-9-17
Dim i As Long
Dim c As Range
Application.ScreenUpdating = False
    For i = 12 To 176 Step 4
    
    For Each c In ThisWorkbook.Sheets("[COLOR=#ff0000]INV[/COLOR]").Range(Cells(2, i), Cells(1000, i))
        If c.Value <> "" Then
        c.Interior.ColorIndex = 3
        Else
        Exit Sub
        End If
    Next
    
    Next
Application.ScreenUpdating = True
End Sub

i tried it right now and get 1004

and i do have correct sheet name INV