VBA Excel - Deleting rows when cell value in column equals zero

1
votes

I have some code in my VBA script that is supposed to delete entire rows when the value of the respective B column of the row equals zero. Unfortunately, the lines of code I have been using are not doing exactly what I want as it deletes the entire row if ANY of the cells in the respective row equals zero. Here is the code so far:

With Worksheets("Sheet2")
For myloop = .Range("B10000").End(xlUp).Row To 1 Step -1
    If .Cells(myloop, 4).Value = 0 Then .Rows(myloop).EntireRow.Delete
Next myloop
End With

For instance I had a row in which the cell in column D was equal to zero and this code deleted the entire row even though the value of the cell in column B was not zero. How can I change the code so it actually only scans for the entries in column B?

Any help is appreciated and thanks in advance.

2
vbaexcel
Change .Cells(myloop, 4).Value to .Cells(myloop, 2).Value – gizlmo
Use Autofilter instead of looping through cells. Autofilter Deletion will be faster than the current code. – Sixthsense
But surely you are only checking one column in the row? Shouldn't you be checking all relevant columns in the row? – MiguelH
@MiguelH At the moment it is enough to only check for the B column in the row. – D. Todor
@Sixthsense At the moment the code works just fine and the whole run with all 400 lines of code is done in about one second. So no need to fasten anything up. At least not yet. :) – D. Todor

2 Answers

1
votes

The user gizlmo provided the answer:

Change .Cells(myloop, 4).Value to .Cells(myloop, 2).Value

1
votes

an AutoFilter() approach is the following:

With Worksheets("Sheet2") '<--| reference your shet
    With .Range("B1", .Cells(Rows.Count, "B").End(xlUp)) '<--| reference its column B range from row 1 (header) down to last not empty row
        If WorksheetFunction.CountBlank(.Cells) + WorksheetFunction.CountIf(.Cells, 0) = 0 Then Exit Sub '<--| exit of no empty or "zero" cells
        .AutoFilter Field:=1, Criteria1:=0, Operator:=xlOr, Criteria2:=""  '<--| filter column B cells with "0" content
         .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete '<--| if any filtered cell other than headers then delete their entire rows
    End With
    .AutoFilterMode = False
End With