Deleting rows if a cell has a "0" Value

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

whoops, still have syntax problems :sniff:

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.

:thumbcoo:

Hi,

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

Sub DeleteZeroes()
Dim lLastRow As Long
Dim Rng As Range


With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
'speed up execution of routine


With ThisWorkbook.Worksheets("Sheet1")
    .Rows(1).Insert                  'Insert dummy row for dummy field name
    .Range("B1").Value = "Temp"      'Insert dummy field name
    .UsedRange                       'Reset last cell
    'Determine last row
    lLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
    'Set Rng to the B column data rows
    Set Rng = .Range("B4", .Cells(lLastRow, "B"))
    'Filter the C column to show only the data to be copied
    Rng.AutoFilter Field:=1, Criteria1:="=0"
    Rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Rows(1).Delete
End With


Set Rng = Nothing
'free memory


With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
'reset application settings


End Sub

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