0

I have a workbook where I want to delete all rows in Sheet "Paste MyStock" column A that don't contain a date and then paste a formula and autofill to the last row.

The code works, however, it won't go through all rows. It suddenly stops after 2-3 rows and I have to run the macro several times. Why is this and how do I fix it?

This is my code:

Sub del_row_not_date()

Dim i As Integer
Dim MyStock As Worksheet
Dim Pivot As Worksheet
Dim Dta As Worksheet

Set MyStock = Sheets("Paste MyStock")
Set Formula = MyStock.Range("O1")
Set PasteFormula = MyStock.Range("N2")

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

LastRow = MyStock.Cells(MyStock.Rows.Count, "A").End(xlUp).Row

For i = 2 To LastRow
    If IsDate(MyStock.Cells(i, 1)) = False Then
        MyStock.Cells(i, 1).EntireRow.Delete
    End If
Next

Formula.Copy
PasteFormula.PasteSpecial xlPasteAll

PasteFormula.AutoFill Destination:=MyStock.Range("N2:N" & LastRow)

With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With   

End Sub
Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
SebastianG
  • 1
  • 1
  • If you need to filter some range by some values and delete filtered rows, you can use filter. Follow my answer [here](https://stackoverflow.com/a/59628305/3187033) where I explain how to properly filter the range. In the end, you just need to call `rngFiltered.EntireRow.Delete`. Deleting using filter is much faster than of a loop. – JohnyL Jan 09 '20 at 17:36

1 Answers1

0

When deleting, loop from the bottom up, otherwise your loop will inadvertently skip rows after deleting:

For i = LastRow to 2 Step -1

Also use Long instead of Integer; see this question why:

Dim i as Long
BigBen
  • 46,229
  • 7
  • 24
  • 40