Delete Row if column E range is empty but has formula inside

[zone709]

Sub Delete2()
Range("E3:E168").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

This works fine if column E is empty, but if there is a formula inside it and even though its blank. It will not delete row. So i think its needs something added if the cells are blank even if a formula is inside it. Still delete the row.

 

[maras]

I don't know what your formulas look like, but try it.

Sub Delete2()
    Range("E3:168").SpecialCells(xlCellTypeFormulas, xlTextValues).EntireRow.Delete
End Sub

 

[zone709]

Hi doesn't work I get message run time 1004 then class not registered

 

[Joe4]

One way:

Sub Delete2()

    Dim r as Long

    Application.ScreenUpdating = False

    For r = 168 to 3 Step -1
        If Cells(r, "E").Value = "" Then Rows(r).Delete
    Next r

    Application.ScreenUpdating =True

End Sub

 

[maras]

Missing Letter "E".My mistake.

Sub Delete2()
    Range("E3:E168").SpecialCells(xlCellTypeFormulas, xlTextValues).EntireRow.Delete
End Sub

 

[zone709]

its breaks Joe. I think its breaking from the tp

 

[zone709]

Hi maras it kind of works now but deletes all the rows even when there is something in it. Column E is pulling in from another sheet so it has formula's but some cells below it also pulls in formula's but cell is empty. Those are the only cells i am trying to delete with row.

 

[Joe4]

its breaks Joe. I think its breaking from the tp

Yes, I had a typo in there, but I fixed it within a minute or two.
You must have copied it before I fixed. Change that "tp" to "to" and it should work.

 

[zone709]

One way:

Sub Delete2()

    Dim r as Long

    Application.ScreenUpdating = False

    For r = 168 to 3 Step -1
        If Cells(r, "E").Value = "" Then Rows(r).Delete
    Next r

    Application.ScreenUpdating =True

End Sub

works thanks

 

[Joe4]

You are welcome.