VBA errors while trying to delete rows based on column value

[boatbabe]

Hi, I am trying to delete rows that have a zero value in column E. I have tried various methods and keep getting 'object does not support this property or method' or 'type mismatch errors. Currently I have:

For deleterow = .Range("E" & Rows.Count).End(xlUp).Row To 1 Step -1
[B]    If .Range("E" & deleterow).Value = 0 Then[/B]
        Rows(deleterow).EntireRow.Delete
    End If
    Next deleterow

I have also tried

'For i = lastRow To 1 Step -1
      '  [B]If .Range("E" & i).Value = 0 Then[/B]
     '       Range("E" & i).EntireRow.Delete
     '   End If
   ' Next i

and

' For i = lastRow To 1 Step -1
    '   [B] If .Range("E" & i).Value2 = 0 Then .Rows(i).Delete[/B]
    
    'Next i

The errors are occurring on the bold lines. Can anyone point me in the direction of what I am doing wrong?

Thanks in advance

Thanks

 

[Fluff]

Firstly you have not changed the lines I highlighted.
Secondly if you are getting a Type Mismatch Error, I suspect that the cell value when you get the error is something like #N/A, #VALUE etc.

 

[billandrew]

I believe you are receiving an error on the last cell in the range #Ref !

One way to correct is the change the if formula to value.

This should work.

The others on this post are the real experts, they may have a more efficient solution.

Range("e1:e" & lastrow).FormulaR1C1 = "=IF(RC[-4]=R[1]C[-4],0,1)"
Range("e1:e" & lastrow).Value = Range("e1:e" & lastrow).Value

 

[Michael M]

Why don't you just use the code in post #2.....it works !!!

 

[billandrew]

Micheal

This is not my post, however I'm interested in learning..

I could be wrong because I'm seldom right... It seems if Column E are formula and not constants Replace Method does not satisfy the OP request.

With Respect...

 

[Michael M]

@billandrew
you're correct, but it's a simple fix !!

Sub DeleteRows()
    On Error Resume Next
With Columns("E")
    [color=red].Value = .Value[/color]
    .Replace 0, "#N/A", xlWhole, , False
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
End With
End Sub

 

[billandrew]

Thanks Michael for the schooling. Truly appreciated.

Cheers!

 

[boatbabe]

Hi all, thanks for all your replies. I did reply last night but my post has disappeared so maybe I never pressed send. @michael you are right, it was because column E contained formulae that compared the row to the one below, however the macro was then deleting rows starting from the bottom, resulting in an #N/A error and causing the macro to crash. All sorted now and works perfectly, thanks again, I love this forum

 

[Michael M]

glad it worked...