CODEWITHSUNDEEP
excelvba
DDV
DDV

Reputation: 2395

How to 'Skip' to Next Row in For Each Range

I am looping through a 2D range object using For Each. By default this loops through columns and then rows. I have a check in a column that indicates whether I should process the row or not. What I am finding is that even if I am not meant to process the row, the program still loops through each column in the given range row - which is hugely inefficient.

Image of an example data set:

enter image description here

Current code:

Sub ForEachLoop()
    
    Dim ws As Worksheet
    Dim myRng As Range, cell As Range
    Dim strCheck As String
    

    Set myRng = Selection 'COLUMN F:G IN IMAGE

    For Each cell In myRng
        strCheck = ws.Range("E" & cell.Row).Value
        If strCheck = "PROCESS" Then
            'do something
        End If
    Next cell
End Sub

I would like to know if there is a way to move to the next row in the range instead of looping through each column in that row. The For loop needs to remain within the For Each loop because I am doing colour formatting on cells.

Upvotes: 2

Views: 1302

Answers (1)

BigBen
BigBen

Reputation: 50162

Two loops: loop the rows, do the check, then loop the cells in the row.

Dim rw As Range
For Each rw in myRng.Rows
   strCheck = ws.Range("E" & rw.Row).Value

   If strCheck = "PROCESS" Then
       Dim cell As Range
       For Each cell in rw.Cells
          'process
       Next
   End If   
Next

Upvotes: 4

Related Questions