Delete Rows Multiple Criteria w/Numbers

[eriknokc]

I am trying to use the code below to eliminate data in a spreadsheet based on certain criteria. I have been able to get this to work with text fields just fine, but now I am running into an issue with numbers. I am trying to get this code to delete rows if they have the word "unknown" in column C and if the value in column B is greater than 900 hours (999:00:00), the numbers in this column are formatted as [hhh]:mm:ss. What should I tweak to make this work?

Sub DeleteRows()
     
    Dim Last_Row As Long
    Dim iLoop As Long
    
    For iLoop = Last_Row To 1 Step -1
        If (Trim(ActiveSheet.Cells(iLoop, 2).Value) > "900") And (Trim(ActiveSheet.Cells(iLoop, 3).Value) = "Unknown") Then
            ActiveSheet.Cells(iLoop, 3).EntireRow.Delete
        End If
    Next iLoop
End Sub

[Richard Buttrey]

Hi,

1. Have you set the variable Last_Row somewhere?
2. Take out the double quotes around the 900.

However I can't help thinking that you'd be better filtering the data and then deleting the filtered rows rather than doing this with a loop. Loops are fine if the data set is relatively small, but with larger data sets you'll find they are much slower than using in built Excel functions such as data filter.

HTH

[eriknokc]

Yes, I forgot that part. I do have the last row set as below. I have tried removing the double quotes, but that does not work.

    Last_Row = ActiveSheet.Range("A65536").End(xlUp).Row + 1

[protonLeah]

Your code,

Trim(ActiveSheet.Cells(iLoop, 2).Value) > "900"

indicates that the values in column B are all text values. Is this the case, or do the cells contain numbers for calculations? If these are meant to be numbers and not text strings then the quotes must be deleted ( 900 is not "900"):

ActiveSheet.Cells(iLoop, 2).Value> 900

Also, I'm not sure from your description whether both conditions must be true or if you want the row deleted if either is true. Do you want the row deleted if either B is greater than 900 OR C is Unknown?

[shg]

Try this:

Sub DeleteRows()
    Dim iRow        As Long

    With ActiveSheet
        For iRow = .Cells(.Rows.Count, "B").End(xlUp).Row To 1 Step -1
            If .Cells(iRow, "B").Value2 > 900 / 24 And _
               InStr(1, .Cells(iRow, "C").Value, "unknown", vbTextCompare) Then
                .Rows(iRow).Delete
            End If
        Next iRow
    End With
End Sub

[eriknokc]

Sorry about not being clearer on that. I was looking for it to require meet both conditions in order for it delete the row.

[eriknokc]

shg Forum Guru, that worked perfect! Thanks everyone for your help and insight into this.

[shg]

You're welcome.

I agree with Richard's comment, BTW.