0

I wrote a macro in vba to input a 1 in the rows when the third or fourth column have blank values and then to delete these rows. There are 1500 lines in the file and the macro skips over certain lines. I have to run the macro several times for it to work properly. Is there a reason for this error.

Sub DEX_Clean()

Dim W As Worksheet
Set W = Worksheets("DEX")

For Each i In W.Range("DEX").Rows
  If i.Cells(1, 3).Value = "" Or i.Cells(1, 4).Value = "" Then
   If i.Cells(1, 1).Value <> "" Then
    i.Cells(1, 7).Value = 1
    End If
   End If
Next i

For Each i In W.Range("DEX").Rows
  If i.Cells(1, 1).Value <> "" And i.Cells(1, 7).Value = 1 Then
  i.Cells(1, 1).EntireRow.Delete
  End If
 Next i

End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
shiggyg123
  • 1
  • 1
  • 2
    When deleting you need to loop from the bottom up. But the best thing to do is to filter the data and delete the visible. Or create a Union range of all the rows to be deleted and delete them all at once. There are many examples on how to do this on this site. – Scott Craner Mar 10 '23 at 18:47
  • 2
    @ScottCraner I wish there was a way to automatically suggest this when someone goes to submit a VBA question on deleting. No offense to the poster, it's a legitimate question...but how many times does this comment get typed? – sous2817 Mar 10 '23 at 18:49
  • 2
    @sous2817 I just know that bigben has these dups listed and can pop them in faster than I can find them. So I comment knowing that it will be closed by BigBen as a dup. – Scott Craner Mar 10 '23 at 18:56

0 Answers0