CODEWITHSUNDEEP
excelvba
Daruki
Daruki

Reputation: 491

Code not deleting every non numeric row

I have this code here which looks through a column of numbers, colors the cells with numbers and deletes the cells and its corresponding rows if it's a nonnumeric entry(the cells are filled with "-"). It deletes some of the non numeric rows but not all of them.

Dim wbI As Workbook, wbO As Workbook
    Dim wsI As Worksheet, wsO As Worksheet
    Dim iCounter As Long
    Dim iCounter1 As Long
    Dim iCounter2 As Long
    Dim lrow As Long, rw As Long
    Dim OutLookApp As Object
    Dim OutLookMailItem As Object
    Dim MailDest As String
    Dim subj As String
    Dim bod As String
    Dim lastrow As Long
    Dim lastrow1 As Long

 lastrow = wsI.Cells(Rows.Count, 4).End(xlUp).Row
lastrow1 = wsO.Cells(Rows.Count, 4).End(xlUp).Row

 With wsO
        For iCounter1 = 2 To lastrow
        If wsO.Cells(iCounter1, 9) > 120 Then

        wsO.Cells(iCounter1, 9).Interior.Color = RGB(255, 101, 101)

        ElseIf wsO.Cells(iCounter1, 9) > 0 And wsO.Cells(iCounter1, 9) < 120 Then

        wsO.Cells(iCounter1, 9).Interior.Color = RGB(169, 208, 142)

        End If
        Next iCounter1

        With wsO
        For iCounter2 = 2 To lastrow

         If Not IsNumeric(Cells(iCounter2, 9)) Then
         wsO.Cells(iCounter2, 9).EntireRow.Delete
         End If

        Next iCounter2


        Rows("2:200").RowHeight = 30
        End With
        End With

Upvotes: 2

Views: 97

Answers (1)

user4039065
user4039065

Reputation:

There seem to be two problems with your code. The primary issue of skipping some rows that should be deleted can be cleared up by looping from the bottom to the top. Failing to work in ths manner may mean that a row is skipped after a row is deleted, the rows are renumbered and you increment on to the next row.

There is a secondary issue where you have implemented a With ... End With statement that references the worksheet to be acted upon and then either reiterate the worksheet reference when referencing cells/ranges/rows or discard it altogether.

With wsO
    For iCounter1 = 2 To LastRow
        If .Cells(iCounter1, 9) > 120 Then
            .Cells(iCounter1, 9).Interior.Color = RGB(255, 101, 101)
        ElseIf .Cells(iCounter1, 9) > 0 And .Cells(iCounter1, 9) < 120 Then
            .Cells(iCounter1, 9).Interior.Color = RGB(169, 208, 142)
        End If
    Next iCounter1

    'this is the primary change
    'loop from the bottom to the top when deleting rows
    For iCounter2 = LastRow To 2 Step -1
        If Not IsNumeric(.Cells(iCounter2, 9)) Then
            .Cells(iCounter2, 9).EntireRow.Delete
        End If
    Next iCounter2

    .Rows("2:200").RowHeight = 30
End With

Note that Cells becomes .Cells and Rows become .Rows. The period (aka . or full stop`) prefix associates each cell/range/row with the parent worksheet referenced in the With ... End With block.

¹ Thanks to Scott Craner for noting the bottom-to-top method in comments.

Upvotes: 3

Related Questions