CODEWITHSUNDEEP
vbadateexcel
fordo19
fordo19

Reputation: 51

Compare a Range Of Dates Against One Date - VBA

I'm not great at VBA, but I've been asked to create a tool for my company whereby the user drops in a list of date information; the information needs to be analyzed and all records with a date prior to a different, static date (below, it's in cell "O2". I've been able to at least delete some records, but it's matching the criteria I need, my records are not being deleted properly:

Sub RemoveDates()
Dim rng As Range
Dim rng2 As Range
On Error Resume Next
Set rng2 = Range("M3:M1002")
For Each rng In rng2
If CDate(rng) < CDate("O2") Then
rng.EntireRow.Delete
Else
End If
Next
End Sub

The dates are all going to be stored in the range M3:M1002, though the number of entries will be variable, they will always be less than 1000. I want to delete the rows that contain dates less than the fixed date in Cell "O2". When I run the macro, I get some dates deleted, but some are still remaining that have a date prior to the date listed in "O2"

Thanks

Upvotes: 1

Views: 1168

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

You must run the loop backwards or use a structure like:

Sub RemoveDates()
    Dim rng As Range
    Dim rng2 As Range
    Dim KillRange As Range
    Dim stdDate As Date
    Set rng2 = Range("M3:M1002")
    Set KillRange = Nothing
    stdDate = CDate(Range("O2"))

    For Each rng In rng2
        If CDate(rng) < stdDate Then
            If KillRange Is Nothing Then
                Set KillRange = rng
            Else
                Set KillRange = Union(KillRange, rng)
            End If
        End If
    Next rng

    If Not KillRange Is Nothing Then
        KillRange.EntireRow.Delete
    End If
End Sub

Upvotes: 2

Related Questions