Skipping Specific Row Within a Loop

[Chirolove]

Good day all,

I have a quick question: I am running a loop which copies certain cells from rows according to an IF statement from a different worksheet.

My code runs from i = 9 to i = 61 but I need it to ignore row 21, as it meets the IF condition yet I still want it to be ignored, how would I go about doing so?

Thanks in advance

Sub CopyTasks ()

Application.ScreenUpdating = False

Dim i As Long
Dim r As Long
r = 12

For i = 9 To 61
    With Sheets("TimeSheet")
        If .Cells(i, "K").Value >= 0.1 And .Cells(i, "K").Value <= 24 Then
            .Cells(i, "B").Resize(, 2).Copy: Sheets("Monday").Cells(r, "C").PasteSpecial xlValues
            .Cells(i, "E").Copy: Sheets("Monday").Cells(r, "E").PasteSpecial xlValues
            .Cells(i, "G").Copy: Sheets("Monday").Cells(r, "I").PasteSpecial xlValues
            r = r + 5
        End If
    End With

Next
Application.ScreenUpdating = True
End Sub

 

[Joe4]

One way:

Application.ScreenUpdating = False

Dim i As Long
Dim r As Long
r = 12

For i = 9 To 61
[COLOR=#ff0000]    If i <>21 Then[/COLOR]
         With Sheets("TimeSheet")
             If .Cells(i, "K").Value >= 0.1 And .Cells(i, "K").Value <= 24 Then
                 .Cells(i, "B").Resize(, 2).Copy: Sheets("Monday").Cells(r, "C").PasteSpecial xlValues
                 .Cells(i, "E").Copy: Sheets("Monday").Cells(r, "E").PasteSpecial xlValues
                 .Cells(i, "G").Copy: Sheets("Monday").Cells(r, "I").PasteSpecial xlValues
                 r = r + 5
             End If
         End With
[COLOR=#ff0000]     End If[/COLOR]
Next
Application.ScreenUpdating = True
End Sub

 

[Chirolove]

Hey Joe4,

Yeah, that's exactly what I needed, now I can get on with the rest of the code. I hadn't excluded rows before so I was unsure how to approach it.

Thanks a bunch and have a nice day!

One way:

Application.ScreenUpdating = False

Dim i As Long
Dim r As Long
r = 12

For i = 9 To 61
[COLOR=#ff0000]    If i <>21 Then[/COLOR]
         With Sheets("TimeSheet")
             If .Cells(i, "K").Value >= 0.1 And .Cells(i, "K").Value <= 24 Then
                 .Cells(i, "B").Resize(, 2).Copy: Sheets("Monday").Cells(r, "C").PasteSpecial xlValues
                 .Cells(i, "E").Copy: Sheets("Monday").Cells(r, "E").PasteSpecial xlValues
                 .Cells(i, "G").Copy: Sheets("Monday").Cells(r, "I").PasteSpecial xlValues
                 r = r + 5
             End If
         End With
[COLOR=#ff0000]     End If[/COLOR]
Next
Application.ScreenUpdating = True
End Sub

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[Peter_SSs]

Another way, particularly useful if there are several values to exclude:

For i = 9 To 61
    Select Case i
      Case 10, 14 To 17, 21
        'Do nothing for the above values/ranges
      Case Else
        With Sheets("TimeSheet")
            If .Cells(i, "K").Value >= 0.1 And .Cells(i, "K").Value <= 24 Then
                .Cells(i, "B").Resize(, 2).Copy: Sheets("Monday").Cells(r, "C").PasteSpecial xlValues
                .Cells(i, "E").Copy: Sheets("Monday").Cells(r, "E").PasteSpecial xlValues
                .Cells(i, "G").Copy: Sheets("Monday").Cells(r, "I").PasteSpecial xlValues
                r = r + 5
            End If
        End With
    End Select
Next

 

[Chirolove]

I ended up using this method for another document, I am quite familiar with the Case statement but I never thought about using it this way!

Thanks a bunch to both of you

Another way, particularly useful if there are several values to exclude:

For i = 9 To 61
    Select Case i
      Case 10, 14 To 17, 21
        'Do nothing for the above values/ranges
      Case Else
        With Sheets("TimeSheet")
            If .Cells(i, "K").Value >= 0.1 And .Cells(i, "K").Value <= 24 Then
                .Cells(i, "B").Resize(, 2).Copy: Sheets("Monday").Cells(r, "C").PasteSpecial xlValues
                .Cells(i, "E").Copy: Sheets("Monday").Cells(r, "E").PasteSpecial xlValues
                .Cells(i, "G").Copy: Sheets("Monday").Cells(r, "I").PasteSpecial xlValues
                r = r + 5
            End If
        End With
    End Select
Next

 

[Peter_SSs]

Thanks a bunch to both of you

You are very welcome. Thanks for the follow-up.