0

I want to drop the rows with zero values in specific columns. However, the macro just works in some rows. I thought that was a format issue so I tried changing the format but it doesn't work. Could you help me find the problem?

Before macro

After macro

    If IsNumeric(Cells(i, 7).Value) And IsNumeric(Cells(i, 8).Value) And IsNumeric(Cells(i, 9).Value) Then
    Else
    Rows(i).EntireRow.Delete
    End If
Next

Worksheets("sheet").Columns("F").NumberFormat = "#,##0.0"
Worksheets("sheet").Columns("G").NumberFormat = "#,##0.0"
Worksheets("sheet").Columns("H").NumberFormat = "#,##0.0"
Worksheets("sheet").Columns("I").NumberFormat = "#,##0.0"

For i = 7 To lastcell
    If Cells(i, 6).Value = 0 And Cells(i, 7).Value = 0 And Cells(i, 8).Value = 0 And Cells(i, 9).Value = 0 Then
    Rows(i).EntireRow.Delete
    End If
Next
BigBen
  • 46,229
  • 7
  • 24
  • 40
Yaneth Gil
  • 61
  • 6
  • 4
    loop backwards: `For i = lastcell to 7 Step -1` – Scott Craner Apr 24 '20 at 17:10
  • @ScottCraner That was the mistake. Thank you. Could you give me a brief explanation? – Yaneth Gil Apr 24 '20 at 17:29
  • see linked post. it explains it. – Scott Craner Apr 24 '20 at 17:31
  • 2
    Note, looping backwards is _one_ solution. A better and much more efficient solution would be to use `Union` to combine the ranges you know you want to delete, and then delete the combined range _once_, after the loop completes: that way you're only hitting the sheet once, and it doesn't matter if you started from the top or from the bottom. – Mathieu Guindon Apr 24 '20 at 17:41

0 Answers0