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

 

[mumps]

Try:

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

 

[mole999]

when you step through what is the lastrow value first time ?

 

[Joe4]

I see that you are putting a period in front of some range references, i.e. .Range("E" & I)
You would only do that if that code is nested within a WITH statement, that designate a range (or sheet) you are already working with.
If this code is not embedded in a WITH statement, it will cause errors. Try removing the periods in front of the word "Range".

 

[boatbabe]

Thanks for your replies.
@Joe4, yes the code is nested in a WITH statement, I have also tried defining the worksheet in the above codes ie

[B] If Worksheets("Accrued Holiday Report").Range("E" & deleterow).Value = 0 Then[/B]
Rows(deleterow).EntireRow.Delete
    End If

but it makes no difference.
@mole999 the last value in the sheet will always be 1
@mumps - that doesn't do anything in my sheet

 

[mole999]

@boatbabe

For i = lastRow To 1 Step -1

I asked for the FIRST Time,, counting down I would expect you to get to 1, but if it is 1 to start with you won't loop

 

[boatbabe]

I asked for the FIRST Time,, counting down I would expect you to get to 1, but if it is 1 to start with you won't loop

Sorry, I'm confused. The first instance of the lastrow value is 1. In the worksheet as I have it at the moment the last row with data in it is row 2198. Column E is a helper column where the rows are assigned the value 1 if the string in column A is the last instance of that string in the list, therefore the value in column e of the last row will always be 1. However if it being the number 1 is the problem I can change the helper column to say yes and no, or true or false, or red and clue, it doesn't matter.

 

[boatbabe]

Here's the whole sub in case i've made a boob somewhere further up

Sub AccruedHolidayReport()


    Dim i As Long
    Dim lastRow As Long
    Dim deleterow As Long
    Dim cell As Range
        
    Worksheets("Data").Range("A:AK").Copy Worksheets("Accrued Holiday Report").Range("A:AK")
    
    With Worksheets("Accrued Holiday Report")
    .Range("A:A,C:C,E:E,G:G,H:H,I:I,J:N,P:AK").Delete
    .Rows("1:1").Delete
        
    End With
        
    With Worksheets("Accrued Holiday Report").Sort
     .SortFields.Add Key:=Range("A1"), Order:=xlAscending
     .SortFields.Add Key:=Range("B1"), Order:=xlAscending
     .SetRange Range("A:D")
     .Apply
        
    lastRow = Range("a" & Rows.Count).End(xlUp).Row
    Range("e1:e" & lastRow).FormulaR1C1 = "=IF(RC[-4]=R[1]C[-4],0,1)"
    
  For deleterow = .Range("E" & Rows.Count).End(xlUp).Row To 1 Step -1
    If .Range("E" & deleterow).Value = 0 Then
       Rows(deleterow).EntireRow.Delete
    End If
    Next deleterow
            
    End With
End Sub

 

[Fluff]

The lines in blue below, are acting on the ActiveSheet not the Accrued Holiday Report

    With Worksheets("Accrued Holiday Report").Sort
     .SortFields.Add Key:=Range("A1"), Order:=xlAscending
     .SortFields.Add Key:=Range("B1"), Order:=xlAscending
     .SetRange Range("A:D")
     .Apply
        
    lastRow = [COLOR=#0000ff]Range("a" & Rows.Count).End(xlUp).Row[/COLOR]
    [COLOR=#0000ff]Range("e1:e" & lastRow).FormulaR1C1 = "=IF(RC[-4]=R[1]C[-4],0,1)"[/COLOR]
    
  For deleterow = .Range("E" & Rows.Count).End(xlUp).Row To 1 Step -1
    If .Range("E" & deleterow).Value = 0 Then
       [COLOR=#0000ff]Rows(deleterow).EntireRow.Delete[/COLOR]
    End If
    Next deleterow
            
    End With

 

[boatbabe]

If I put in the Worksheet reference then the error changes from 'object does not support property' to 'type mismatch', but it's still on the same (bold) line

Sub AccruedHolidayReport()

    Dim i As Long
    Dim lastRow As Long
    Dim deleterow As Long
    Dim cell As Range
        
    Worksheets("Data").Range("A:AK").Copy Worksheets("Accrued Holiday Report").Range("A:AK")
    
    With Worksheets("Accrued Holiday Report")
    .Range("A:A,C:C,E:E,G:G,H:H,I:I,J:N,P:AK").Delete
    .Rows("1:1").Delete
        
    End With
        
    With Worksheets("Accrued Holiday Report").Sort
     .SortFields.Add Key:=Range("A1"), Order:=xlAscending
     .SortFields.Add Key:=Range("B1"), Order:=xlAscending
     .SetRange Range("A:e")
     .Apply
     
     End With
        
    lastRow = Worksheets("Accrued Holiday Report").Range("a" & Rows.Count).End(xlUp).Row
    Range("e1:e" & lastRow).FormulaR1C1 = "=IF(RC[-4]=R[1]C[-4],0,1)"
   
    For deleterow = Worksheets("Accrued Holiday Report").Range("E" & Rows.Count).End(xlUp).Row To 1 Step -1
    If Worksheets("Accrued Holiday Report").Range("E" & deleterow).Value = 0 Then
        Rows(deleterow).EntireRow.Delete
    End If
    Next deleterow
        
End Sub