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