vba - code to delete row if a column contains a zero

[parkerbelt]

I'm looking for the vba code to delete a row if a column contains a zero

 

[Mike LH]

Hi,

Try this, change the column letter to the correct one.

Sub delete_Me()
Dim MyColumn As String, LastRow As Long
Dim x As Long
MyColumn = "B"
LastRow = Cells(Cells.Rows.Count, MyColumn).End(xlUp).Row
For x = LastRow To 1 Step -1
    If Len(Cells(x, MyColumn)) = 1 And Cells(x, MyColumn).Value = 0 Then
        Rows(x).Delete
    End If
Next
End Sub

 

[parkerbelt]

Hi,

Try this, change the column letter to the correct one.

Sub delete_Me()
Dim MyColumn As String, LastRow As Long
Dim x As Long
MyColumn = "B"
LastRow = Cells(Cells.Rows.Count, MyColumn).End(xlUp).Row
For x = LastRow To 1 Step -1
    If Len(Cells(x, MyColumn)) = 1 And Cells(x, MyColumn).Value = 0 Then
        Rows(x).Delete
    End If
Next
End Sub

I got a Run-time error '13':
Type mismatch

Error when I tried that code and this line is highlighted yellow when I debug it:
If Len(Cells(x, MyColumn)) = 1 And Cells(x, MyColumn).Value = 0 Then

 

[Rick Rothstein]

Does this macro work for you...

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

 

[parkerbelt]

Does this macro work for you...

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

That didn't seem to work either.
I stepped through the code and it just went straight through to End Sub and never looped.

 

[Sektor]

Using AutoFilter is the fastest method I know of. Here's example of how to use it:

Sub FastRemove()


    Dim rngData As Range
    Dim rngWithoutHeader As Range
    Dim rngDelete As Range
    
    Set rngData = Range("A1:E7")


    With rngData
        Set rngWithoutHeader = .Rows("2:" & .Rows.Count)
    End With


    rngData.AutoFilter Field:=1, Criteria1:=0 'Filter by zero


    On Error Resume Next 'Error handling is necessary in case if nothing is filtered
    Set rngDelete = rngWithoutHeader.SpecialCells(xlCellTypeVisible)
    If Err = 0 Then
        rngDelete.EntireRow.Delete
    End If


    rngData.AutoFilter 'Remove autofilter


End Sub

 

[Mike LH]

Hi,

That will be because you have errors in the range.

Sub delete_Me()
Dim MyColumn As String, LastRow As Long
Dim x As Long
MyColumn = "B"
LastRow = Cells(Cells.Rows.Count, MyColumn).End(xlUp).Row
On Error Resume Next
For x = LastRow To 1 Step -1
    If Not (IsError(Cells(x, MyColumn))) Then
    If Len(Cells(x, MyColumn)) = 1 And Cells(x, MyColumn).Value = 0 Then
        Rows(x).Delete
    End If
    End If
Next
On Error GoTo 0
End Sub

 

[Rick Rothstein]

That didn't seem to work either.
I stepped through the code and it just went straight through to End Sub and never looped.

Then I don't think the cells you say have a 0 in them contain only a 0 all by itself in them... there may be a non-visible character in with them as well (a space or a non-breaking space, ASCII 160). Let's say B5 contains what you think is a 0 (and also is a cell that my code did not change), put this formula in an empty cell and see if the value is more than 1...

=LEN(B5)

If it is more than 1, then your 0 has company with it in the cell.

 

[parkerbelt]

Using AutoFilter is the fastest method I know of. Here's example of how to use it:

Sub FastRemove()


    Dim rngData As Range
    Dim rngWithoutHeader As Range
    Dim rngDelete As Range
    
    Set rngData = Range("A1:E7")


    With rngData
        Set rngWithoutHeader = .Rows("2:" & .Rows.Count)
    End With


    rngData.AutoFilter Field:=1, Criteria1:=0 'Filter by zero


    On Error Resume Next 'Error handling is necessary in case if nothing is filtered
    Set rngDelete = rngWithoutHeader.SpecialCells(xlCellTypeVisible)
    If Err = 0 Then
        rngDelete.EntireRow.Delete
    End If


    rngData.AutoFilter 'Remove autofilter


End Sub

Tried this and it didn't work...My filters were deleted however. Thanks though.

 

[parkerbelt]

Hi,

That will be because you have errors in the range.

Sub delete_Me()
Dim MyColumn As String, LastRow As Long
Dim x As Long
MyColumn = "B"
LastRow = Cells(Cells.Rows.Count, MyColumn).End(xlUp).Row
On Error Resume Next
For x = LastRow To 1 Step -1
    If Not (IsError(Cells(x, MyColumn))) Then
    If Len(Cells(x, MyColumn)) = 1 And Cells(x, MyColumn).Value = 0 Then
        Rows(x).Delete
    End If
    End If
Next
On Error GoTo 0
End Sub

When I ran your code, I had some formulas in there, but no errors. When your code ran, I ended up with an error in one cell. I fixed the formula back the way that I had it and then copied and pasted special values, so there were no formulas in there and then ran your code again and it worked.

Thanks for your help!