如果日期不匹配，请删除一行 [英] Delete a row if the date does not match

问题描述

我有约20,000行数据按日期排序。有时，有时间不匹配（即一个时间序列比另一个引用更频繁）。如果时间与列A的时间不匹配，我想删除从列D开始的行。

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.

我写了以下代码，但是我收到错误（运行时错误1004）。我不知道问题在哪里。我认为这可能是WorksheetFunction。

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

推荐答案

可能：

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

这篇关于如果日期不匹配，请删除一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！