CODEWITHSUNDEEP
vbaexcel
user1885099
user1885099

Reputation: 150

If cells in specific sheet equal a specific value, then delete the row

I am checking all cell in a range. If a cell's value is equal to 0 (the value is generated by a formula) then delete the row.

Sub CleanJunk()
    For Each c In Worksheets("testsheet").Range("B33:B533").Cells
        If c.Value = "0" Then Rows(c.Row).EntireRow.Delete
    Next
End Sub

Right now the module runs without any error. It just doesn't do anything. No rows get deleted or affected in any way.

Upvotes: 2

Views: 1577

Answers (2)

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

The key here is that the value is generated by a formula. It probably contains a floating point error.

Comparing a value to a string "0" isn't good coding either - apples with apple not oranges!

To get rid of the floating point error use the `ROUND(Num, # of decimals) with # of decimals = 0

Compare the result to 0.

Option Explicit 'so you don't confuse your variables
Sub CleanJunk()
Dim c as range
    For Each c In Worksheets("testsheet").Range("B33:B533").Cells
        'compare values rounded to integer with 0
        If Round(c.Value, 0) = 0 Then c.EntireRow.Delete
    Next c 'it pays to be explicit
End Sub

Upvotes: 0

Rory
Rory

Reputation: 34045

You haven't qualified the Rows call, and you need to loop backwards:

Sub CleanJunk()
  Dim n as long
    for n = 533 to 33 step -1
    If Worksheets("testsheet").Cells(n, "B").Value2 = 0 Then Worksheets("testsheet").Rows(n).EntireRow.Delete
    Next
End Sub

Upvotes: 5

Related Questions