CODEWITHSUNDEEP
excelvbarangeworksheet-function
user3488564
user3488564

Reputation: 5

Delete a row if the date does not match

I've got about 20,000 lines of data ordered by date. Sometimes, there's a time mismatch (ie one time series is quoted more frequently than the other). I want to delete the row starting at column D if the time does not match that of column A.

I've written the following code, but I'm getting errors (runtime error 1004). I'm not sure where's the problem. I think it might be the WorksheetFunction.

Dim rng As Range
Dim c As Variant
Dim myVal As Boolean

Sub rectifyData()
    Set rng = Range("A4:A20459")
    For Each c In rng.Rows
        myVal = xlApp.WorksheetFunction.If(c = ActiveSheet.Range(c).Offset(0, 4), 0, 1)
        If (myVal = 1) Then
            ActiveSheet.Range(c).Offset(0, 4).Rows.Delete
            myVal = 0
        End If
    Next c
End Sub

Upvotes: 0

Views: 210

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Perhaps:

Sub RowKiller3()
    Dim rDel As Range
    Dim r As Range, rBig As Range

    Set rBig = Range("A4:A20459")
    Set rDel = Nothing
    For Each r In rBig
        If r.Value <> r.Offset(0, 3) Then
            If rDel Is Nothing Then
                Set rDel = r
            Else
                Set rDel = Union(rDel, r)
            End If
        End If
    Next r

    If Not rDel Is Nothing Then
        rDel.EntireRow.Delete
    End If

End Sub

Upvotes: 1

Related Questions