Skipping Specific Row Within a Loop
Results 1 to 7 of 7

Thread: Skipping Specific Row Within a Loop

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Skipping Specific Row Within a Loop

    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

    Code:
    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
    Last edited by Chirolove; Aug 12th, 2019 at 08:34 AM.

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,456
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Skipping Specific Row Within a Loop

    One way:
    Code:
    Application.ScreenUpdating = False
    
    Dim i As Long
    Dim r As Long
    r = 12
    
    For i = 9 To 61
        If i <>21 Then
             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 If
    Next
    Application.ScreenUpdating = True
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Skipping Specific Row Within a Loop

    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!

    Quote Originally Posted by Joe4 View Post
    One way:
    Code:
    Application.ScreenUpdating = False
    
    Dim i As Long
    Dim r As Long
    r = 12
    
    For i = 9 To 61
        If i <>21 Then
             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 If
    Next
    Application.ScreenUpdating = True
    End Sub

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,456
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Skipping Specific Row Within a Loop

    You are welcome.
    Glad I was able to help!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,483
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Skipping Specific Row Within a Loop

    Another way, particularly useful if there are several values to exclude:
    Code:
    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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    New Member
    Join Date
    Jul 2019
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Skipping Specific Row Within a Loop

    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


    Quote Originally Posted by Peter_SSs View Post
    Another way, particularly useful if there are several values to exclude:
    Code:
    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

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,483
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Skipping Specific Row Within a Loop

    Quote Originally Posted by Chirolove View Post
    Thanks a bunch to both of you
    You are very welcome. Thanks for the follow-up.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •