0

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!

squar_o
  • 557
  • 2
  • 11
  • 36

1 Answers1

1

You need to traverse your K loop in reverse order. Otherwise rows will be skipped as you delete because they are shifted up by the delete operation and your K value increments over them.

For K = endrow To 2 Step -1
    If CDec(.Cells(K, 19).Value) < CDec(0.501) Then
        .Range("S" & K).EntireRow.Delete
    End If
Next
Jason Faulkner
  • 6,378
  • 2
  • 28
  • 33
  • Cheers. That makes perfect sense, however I think my original code has far more problems as it still leaves some values <0.501 and not all sheets are sorted descending - I will work on debugging the sheets loop tomorrow. – squar_o Mar 18 '15 at 18:15
  • 1
    @squarah - It may be as simple as casting to a double to do the comparisions using the `CDbl` function. See updated answer. – Jason Faulkner Mar 18 '15 at 18:21
  • That doesn't seem to change things unfortunately. I see that 0.501 is a double but what exactly is casting to a double? Cheers! – squar_o Mar 18 '15 at 18:29
  • 1
    @squarah - Actually the safest thing to do would be to cast both sides as a decimal. You do this with the `CDec` function (see updated answer). Double comparison can sometimes be a bit wonky. – Jason Faulkner Mar 18 '15 at 19:18
  • 1
    thanks for your help, that part is working nicely now. I have posted the other problems I am experiencing with the same code to another question [link](http://stackoverflow.com/questions/29143398/vba-sort-decending-not-sorting-unpredictable-looping) – squar_o Mar 19 '15 at 11:46