Hi i am trying to find a quick method of deleting a large number of rows in a sheet if a certain cell has a zero in it then delete the row.
This takes 40 seconds looping through the rows so tried altering a bit of code i found but got stuck.

Column I has the rows with zeros in. I insert a column and fill it with a formula which puts a 1 in any row to remove. Then i sort the column. This all works.

Then i need to delete the rows using the line .Columns(5).SpecialCells(xlCellTypeConstants, 4).EntireRow.Clear but obvioulsy the 1 in the cell is not a constant that the code is looking for.

I tried googling excel constants but did not get very far.

Can anyone help?

Thanks

Neil


Sub RemZeroRows()
Sheets("Sheet2").Activate
'Application.ScreenUpdating = False
With Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 5)
    .Cells(5).EntireColumn.Insert
    .Columns(5).Formula = "=IF(J1=0, 1)"
    .Value = .Value
    .Sort .Cells(5), xlAscending, Header:=xlNo
    On Error Resume Next
    .Columns(5).SpecialCells(xlCellTypeConstants, 4).EntireRow.Clear
    .Columns(5).EntireColumn.Delete
End With
Application.ScreenUpdating = True
End Sub