+ Reply to Thread
Results 1 to 11 of 11

second row in loop doesn't always delete. Please Help

Hybrid View

  1. #1
    Registered User
    Join Date
    05-03-2017
    Location
    London, England
    MS-Off Ver
    2010-2016
    Posts
    4

    second row in loop doesn't always delete. Please Help

    I am writing a macro that checks to see if a cell, and the cell directly below it within the same column are the same. So for instance it will start with z2, then compare z2 to z3. Only if z2 and z3 are the same value , then the macro will take the sum of the two corresponding cells in a different column of their respective rows i.e. b2 + b3. Only if b2+b3 equals 0, then i want the macro to delete the rows 2 + 3, then proceed to check if z4 (or z3 if not previously erased) is the same as z5 (or z4 again depending upon scenario) and so forth until the column runs out. My problem is sometimes both rows get deleted - and other times only 1 of the two rows get deleted and i don't know why some are and some aren't.

    Code so far. Please be aware I am still a vba novice. Feel free to change my code however you may see fit.
    Sub CompareZ_Values()
    
    Dim firstIndex, secIndex, firstQ, secondQ As Integer
    firstIndex = 2
    secIndex = 3
    firstQ = 2
    secondQ = 3
    
    Do
        If Cells(firstIndex, 26).Value = Cells(secIndex, 26).Value Then 'check if the Z2 is equal to Z3, if it is move on into the loop
            If Cells(firstQ, 21).Value + Cells(secondQ, 21).Value = 0 Then 'check if sum U2 + U3 = 0, if it is move on
                Rows(firstQ).Select 'select first row to delete
                Selection.EntireRow.Delete
                Rows(firstQ).Select 'select second row to delete
                Selection.EntireRow.Delete 'this is the part where I know the root of the problem lies 
        End If
        End If
        firstIndex = firstIndex + 1
        secIndex = secIndex + 1
        firstQ = firstQ + 1
        secondQ = secondQ + 1 'this is me trying to tell the macro to move onto the next cell be it Z3 or Z4 depending on if Z3 gets erased or not
    
    Loop While Cells(firstIndex, 28).Value <> "" And Cells(secIndex, 28).Value <> "" 'checks to see if cells are NULL, if not NULL than initiate the loop
    
    End Sub
    EDIT: i cannot seem to upload a sample spreadsheet. I am trying to fix this and get one up asap.
    Last edited by johndoe15; 05-09-2017 at 09:47 PM.

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: second row in loop doesn't always delete. Please Help

    Maybe

    Sub test()
    With ActiveSheet
        For x = .Range("Z" & Rows.Count).End(xlUp).Row To 2 Step -2
            If .Cells(x, 26) = .Cells(x - 1, 26) Then
                If .Cells(x, 21).Value + .Cells(x - 1, 21).Value = 0 Then
                    .Range("A" & x - 1, "A" & x).EntireRow.Delete
                End If
            End If
        Next
    End With
    End Sub
    Kind regards
    Leo

  3. #3
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,520

    Re: second row in loop doesn't always delete. Please Help

    This should do it, When deleting rows in loop best to loop from the bottom to the top

    Sub abc()
     For irow = Cells(Rows.Count, 26).End(xlUp).Row To 2 Step -1
        If Cells(irow, 26).Offset(-1).Value = Cells(irow, 26).Value  
            If Cells(irow, 21).Offset(-1).Value + Cells(irow, 21).Value = 0 Then
                Rows(irow).Delete
            End If
        End If
     Next
    End Sub
    Last edited by mike7952; 05-09-2017 at 10:27 PM.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  4. #4
    Registered User
    Join Date
    05-03-2017
    Location
    London, England
    MS-Off Ver
    2010-2016
    Posts
    4

    Re: second row in loop doesn't always delete. Please Help

    Thank you both for your response. I got a run error with the debugger pointing to the "End If" in mike's code, though i am sure it is likely an error on my end
    Leo - your code worked almost perfectly! so i still can't insert a spreadsheet (idk why im still trying) but leo your code did erase the two lines, but not in the other 4 remaining ones. Going to to my best to improv a sheet at this point (going to use | to seperate columns) bold are the rows that should be deleted:

    1245 |filler .... filler | 3000
    1234 |filler .... filler | 3000
    1245 |filler .... filler | 3000
    -4000|filler .... filler | 3001
    8888 |filler .... filler | 2222
    -8888|filler .... filler | 2222
    ' these are the two line's leo's code did erase
    4001 |filler .... filler | 3001
    2000 |filler .... filler | 3002
    -2000|filler .... filler | 3003
    -6000|filler .... filler | 3004
    6000 |filler .... filler | 3005
    -4567|filler .... filler | 3006
    4567 |filler .... filler | 3006
    7894 |filler .... filler | 3007
    -7894|filler .... filler | 3007
    ' these were the lines missed
    4891 |filler .... filler | 4895
    -1234 |filler .... filler | 7008
    -1234 |filler .... filler | 7008
    4578 |filler .... filler | 1346
    -4578|filler .... filler | 4679

    Thank you both so much for the help on this!

  5. #5
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: second row in loop doesn't always delete. Please Help

    to upload a sample spreadsheet
    Choose Go Advanced right down corner
    scroll down till you see manage Attachments

  6. #6
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,520

    Re: second row in loop doesn't always delete. Please Help

    Quote Originally Posted by johndoe15 View Post
    Thank you both for your response. I got a run error with the debugger pointing to the "End If" in mike's code
    I fixed my code in this post Here

  7. #7
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: second row in loop doesn't always delete. Please Help

    I think that you just have to move these lines into your second IF statement because you don't want to increase your counter if you just deleted 2 rows.
    If Cells(firstIndex, 26).Value = Cells(secIndex, 26).Value Then 'check if the Z2 is equal to Z3, if it is move on into the loop
            If Cells(firstQ, 21).Value + Cells(secondQ, 21).Value = 0 Then 'check if sum U2 + U3 = 0, if it is move on
                Rows(firstQ).Select 'select first row to delete
                Selection.EntireRow.Delete
                Rows(firstQ).Select 'select second row to delete
                Selection.EntireRow.Delete 'this is the part where I know the root of the problem lies 
            Else 'Because you want to increase your counter only if you have not deleted any row.
              firstIndex = firstIndex + 1
              secIndex = secIndex + 1
              firstQ = firstQ + 1
              secondQ = secondQ + 1
           End If
        Else 'If the two rows are not equal, you want to increase your counter.
              firstIndex = firstIndex + 1
              secIndex = secIndex + 1
              firstQ = firstQ + 1
              secondQ = secondQ + 1
        End If
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  8. #8
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: second row in loop doesn't always delete. Please Help

    Maybe have to check for things like

    "-7894"
    "7894"
    "7894 "

    in this case all 3 different and no rows to delete

    Kind regards
    Leo

  9. #9
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: second row in loop doesn't always delete. Please Help

    Like this


    Kind regards
    Leo
    Attached Images Attached Images

  10. #10
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: second row in loop doesn't always delete. Please Help

    Or

    Sub test()
    With ActiveSheet
        For x = .Range("Z" & Rows.Count).End(xlUp).Row To 2 Step -1
            If .Cells(x, 26) = .Cells(x - 1, 26) Then
                If .Cells(x, 21).Value + .Cells(x - 1, 21).Value = 0 Then
                    .Range("A" & x - 1, "A" & x).EntireRow.Delete
                    x = x-1
                End If
            End If
        Next
    End With
    End Sub

  11. #11
    Registered User
    Join Date
    05-03-2017
    Location
    London, England
    MS-Off Ver
    2010-2016
    Posts
    4

    Re: second row in loop doesn't always delete. Please Help

    THANK YOU EVERYONE - everyone's code worked for me in the end. I cannot thank you all enough. Going to go ahead and mark this thread solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. help on a Do loop that doesn't work
    By g_liron in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-12-2015, 10:50 AM
  2. [SOLVED] Doesn't recognize the loop?
    By AllThingsIan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-13-2014, 04:34 AM
  3. [SOLVED] Do until loop that doesn't seem to end
    By Sc0tt1e in forum Excel Programming / VBA / Macros
    Replies: 31
    Last Post: 03-31-2014, 06:07 PM
  4. Replies: 7
    Last Post: 10-23-2012, 08:38 AM
  5. [SOLVED] For Each loop doesn't take every row into account
    By Tino XXL in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-15-2012, 07:54 AM
  6. Macro Loop Broken. Detects Max but doesn't continue loop
    By herchenbach in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-21-2011, 12:17 PM
  7. My loop doesn't work why not
    By ljh66 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-06-2007, 01:02 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1