Trying to Delete Rows within a Loop

[DanSand]

I am looping through cells in a coulmn, and if the current cell and the cell below are both empty i would like to delete the entire row im currently in.

The code below is what i tried but it is not working. What is the solution?

For i = firstrow To 5000
    If Cells(i, fincol) = "" And Cells(i + 1, fincol) = "" Then
        Rows("i:i").EntireRow.Delete
    End If
Next i

 

[chergh]

Rows(i).EntireRow.Delete

 

[ecawilkinson]

Are you trying to do this in Excel, rather than Access? If so change the delete line to:

Rows(i).delete

HTH,
Chris

 

[chergh]

It's also worth noting that this will miss data. When you delete row(i) then row(i+1) will become row(i) and this row will then be missed when the counter is incremented to i+1.

 

[ecawilkinson]

dan,
Chergh is right about missing data, you could try stepping the loop down, i.e.

For i = 4999 to FirstRow  step -1

Chris

 

[gemma-the-husky]

rather than use explicit row numbers, you ought to do this by iterating a recordset.

stuff like this often idicates normalization issues though.

 

[chergh]

Think he is doing this in excel, not access.