0

I have the following code:

Sub test() Dim nws As Worksheet 'new sheets to add template Dim tws As Worksheet Dim Cht As ChartObject Dim i As Range Dim sp As Chart 'for scatterplot Dim zone As Range Set tws = ThisWorkbook.Sheets("Template") For Each nws In ThisWorkbook.Worksheets
    If nws.Name <> "Template" And nws.Name <> "Protocol" And nws.Name <> "Record Sheet" Then
        nws.Select
        Set zone = nws.Range("D2", Range("D2").End(xlDown))
        zone.NumberFormat = "0.00"
        For Each i In zone
            If i.Value < 0 Then
            i.EntireRow.Delete
            End If
        Next i
    End If Next nws

End Sub

When a cell is less than zero it deletes the entire row. The problem with this is that the row below it moves up and becomes the active cell, therefore it essentially get "skipped" when the code runs 'next i'. This "skipped" cell may contain a -1 and doesnt get deleted because it essentially got skipped in the process.

I was wondering how to go back one row after the current row gets deleted?

I've tried:

i.offset(-1,0).activate

OR

Activecell.offset(-1,0).activate

But i just get error messages

  • 6
    Always go backward when deleting rows/cells – Vincent G Jan 24 '22 at 09:00
  • 6
    Use an index `For` loop and loop backwards instead ([example](https://stackoverflow.com/questions/45973888/for-loop-in-excel-vba-only-deleting-every-2nd-row?noredirect=1&lq=1)). Or you can delete all the rows at once using `Union` ([example](https://stackoverflow.com/questions/56473206/deleting-rows-with-reverse-loop-vb)) – Raymond Wu Jan 24 '22 at 09:00
  • thank you, i didnt know about those techniques. works perfectly now –  Jan 24 '22 at 09:29

0 Answers0