delete rows

[checoturco]

hi all,

i have a code to delete a row if a cell has a 0(zero) value, this is the code:

For i = 1 To 1000
If .Cells(i, "E").Value = 0 Then
.EntireRow(i).Delete
enf if
next

what happens it´s that if i had 2 cells with 0, i.e.

cell(1E)=0
cell(2E)=0

it delete the row cell(1E) and don´t delete row with cell(2E) because this cell becames cell(1E) and next loop goes to next cell.

somebody has any idea to fix this?


tks

 

[KenHigg]

Should this be in the Excel forum?

 

[checoturco]

well,

i´m running this code in macro that i have in a excel sheet....
don´t seem me that this post could be posted in module/vba

checo

 

[ghudson]

This thread belongs in the Excel forum since it relates to Excel, not Access.

Try this...

Sub Delete_Row_If_Value_Matches()
    
    Application.ScreenUpdating = False
    
    Dim R As Long
 
    For R = 500 To 2 Step -1 [COLOR="Green"]'500 = last row and 2 = first row to check[/COLOR]
    If Cells(R, 1).Value = 0 Then Rows(R).Delete [COLOR="green"]'1 = first column A[/COLOR]
    
    Next R

    Application.ScreenUpdating = True


End Sub