lundi 2 janvier 2017

How to prevent row check being skipped when the previous row is checked and deleted?

This is intended to loop through two columns and verify that the value in the L column is lower than a specific (single) value in a cell from another sheet. It also checks to see if there is an "#N/A" error in the cell on the same row in column M. If these are true then the entire row is deleted. The code below appears to work, however, I have to run the For loop multiple time to get fully delete all the rows. My hunch is that when a row is deleted it's not checking the one right below it and moving on. How can I avoid this? Any help is appreciated.

Sub removerows()

Dim wsOut As Worksheet
Dim wsPrev As Worksheet
Dim r As Long
Dim Lastrow As Long

Set wsOut = Worksheets("Output")
Set wsPrev = Worksheets("Previous")
Lastrow = wsOut.UsedRange(wsOut.UsedRange.Cells.Count).Row

For r = 2 To Lastrow
    If wsOut.Cells(r, "L").Value < wsPrev.Cells(2, "L").Value And _
        Application.WorksheetFunction.IsNA(wsOut.Cells(r, "M").Value) Then
              wsOut.Cells(r, "L").EntireRow.Delete
        Else
            wsOut.Cells(r, "L").Interior.ColorIndex = 20
    End If
Next

End Sub

Aucun commentaire:

Enregistrer un commentaire