mercredi 18 mars 2015

VBA Delete entire row if value is less than specified value - deletes wrong rows

I'm attempting to run a small macro that searches column S of a worksheet and if the value in column S is <0.501, it deletes that row.


The code I am running at the moment deletes some rows but appears to do so randomly rather than based on the cell value in s. Can anyone see where my error is coming from?



Sub sort_delete_500cust()

Dim WS_Count As Integer
Dim I, K As Integer
Dim endrow As Long

' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = Workbooks("Standard.xlsx").Worksheets.count

' Begin the loop.
For I = 1 To WS_Count

With Worksheets(I)

endrow = .Range("a" & .Rows.count).End(xlUp).row ' only works if cells are unmerged
Range("A2:v2" & endrow).Sort _
Key1:=Range("s2"), Order1:=xlDescending 'key is the sort by column

For K = 2 To endrow

If .Cells(K, 19).Value < 0.501 Then
.Range("S" & K).EntireRow.Delete

End If

Next K

End With

Next I

End Sub


Cheers!


Aucun commentaire:

Enregistrer un commentaire