jeudi 7 septembre 2017

How to delete rows if cells abide by if conditions

I have created a macro to delete entire row if a range of cells of the row abides by an if condition. The problem so far is that the macro is actually not deleting all the rows that should be deleted and I have no idea why. I have tested numerous times but no result. I should mention that the range is copy pasted from another worksheet, but I have already made this part work. So, I dont think there is a problem there. I am providing a screenshot of my excel sheet and the code below.

enter image description here

Sub a()

Dim lastrow3 As Long

Sheets.Add.Name = "Report_Copy"
Worksheets("Report").Cells.Copy

With Worksheets("Report_Copy")
    .Cells.PasteSpecial xlValues
    .Cells.PasteSpecial xlFormats
End With
With Worksheets("Report_Copy").UsedRange
    .Value = .Value
End With

Sheets("Report_Copy").Select
lastrow3 = ThisWorkbook.Sheets("Report_Copy").Cells(Sheets("Report_Copy").Rows.Count, "K").End(xlUp).Row
For Each cell In Sheets("Report_Copy").Range("L25:L" & lastrow3)
    If cell.Value < 0.5 And cell.Offset(0, 1).Value < 0.5 And cell.Offset(0, 2).Value < 0.5 And cell.Offset(0, 3).Value < 0.5 And cell.Offset(0, 4).Value < 0.5 And cell.Offset(0, 5).Value < 0.5 And cell.Offset(0, 6).Value < 0.5 And cell.Offset(0, 7).Value < 0.5 And cell.Offset(0, 8).Value < 0.5 And cell.Offset(0, 9).Value < 0.5 Then
        cell.EntireRow.Delete
    End If
Next cell

End Sub

Aucun commentaire:

Enregistrer un commentaire