Deleting rows if a cell has a "0" Value

  • Hi, I was trying to write a macro for a spreadsheet I have. What I would like to do is, between a range of cells, if the cell value is 0 then delete the entire row. Now I have been working on code for this, but I have failed every time with the macro deleting all rows. Here are some snipits from my code.



    Sub delete()
    For Each C In Worksheets("Sheet2").Range("B4:B3556")
    If C.Value = 0 Then
    Rows.delete
    End If
    Next C
    End Sub


    Any help with this would be Great.


    :guitar:

  • You have to select the row before you can delete it.
    You may also want to use a rangename instead of b4:b3556 otherwise, as you delete rows, you will have zeros at the bottom of your range.


    Sub delete()
    For Each C In Worksheets("Sheet2").Range("B4:B3556")
    If C.Value = 0 Then
    data_row = C.Row
    Cells(data_row, 1).Select
    Rows.delete
    End If
    Next C
    End Sub

  • Actually, you don't need to select a row to delete it. Try this code.


    Sub delete()
    Range("B4").EntireRow.Insert
    Range("B4").Value = "Temp"
    Range("B4:B3557").AutoFilter Field:=1, Criteria1:="=0", Operator:=xlAnd
    Range("B4:B3557").SpecialCells(xlCellTypeVisible).EntireRow.delete
    End Sub



    Hope this helps you out.

    Barrie Davidson
    My Excel Web Page
    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  • The macro ran, but nothing happened. What I have is 2 Colums A and B. Column A has the material names and Column B has the dollar value. If the dollar value = 0, then I want to delete that row. I could manually do this, but as you can see, there's 3500 lines to go through. To do this for 10 accounts will be a huge pain in the rear end. If you have any other ideas, I'm open to suggestions.


    :cheers:

  • Hi,


    Probably the most efficient way to do this sort of deletion is to use Excel's inbuilt filtering abilities. See below:

    HTH


    PS Just noticed the dollar reference. Do you have a heading "Dollars" and then numeric values in the rows beneath or are the cells actually formatted as currency?

  • Another way of doing this not using macros is as follows. In the adjasent column to your data type in an if formula -if(cell=0,"T",1) and copy it down to your data range. Then select the formula range and edit>goto>special>text. This will select all the rows that have 0 values. Now edit>delete>row.


    Hope this helps.
    Rennie

  • Small Modifications for your code shoudl work.


    Sub delete()
    Worksheets("Sheet2").activate


    For i = 3556 to 4 step -1
    if (cells(i,2).value = 0) then
    ii = i &":" i
    rows(ii).select
    selection.Entirerow.delete
    End If


    Next i
    End Sub
    Hope this helps.


    Ram P

  • Quote

    Originally posted by Rennie
    Another way of doing this not using macros is as follows. In the adjasent column to your data type in an if formula -if(cell=0,"T",1) and copy it down to your data range. Then select the formula range and edit>goto>special>text. This will select all the rows that have 0 values. Now edit>delete>row.


    Hope this helps.
    Rennie


    Thank You very Much. This solved my problem.


    :cheers:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!