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.
Bookmarks