Deleting series of dates

[howard]

Each month I import data. I would like I macro that will enable me to select the month/s that I wish to retain in Col A and to delete all the other rows. For EG Column A muy contain 25/02/2011, 20/01/2011, 25/03/2011, 19/03/2011 (date in format dd/mm/yyyy)

Say I want to retain March (03), then in the example above the rows containing anything prior to 25/03/2011 must be deleted i.e the rows containing 25/03/2011, 19/03/2011 must be retained and all the other rows prior to 03 containing the date must be deleted

Your assistance will be most appreciated

 

[JoeMo]

Try this:

Sub RemoveMonths()
Dim lRw As Long, c As Range, mnthNum As String, delRws As Range, ct As Long
lRw = Range("A" & Rows.Count).End(xlUp).Row
mnthNum = InputBox("Enter a Month number from 1 to 12")
If mnthNum = "" Then Exit Sub
mnthNum = Val(mnthNum)
For Each c In Range("A1", "A" & lRw)
    If IsDate(c.Value) Then
        If Month(c.Value) <> mnthNum Then
            ct = ct + 1
            If delRws Is Nothing Then
                Set delRws = c
            Else
                Set delRws = Union(c, delRws)
            End If
        End If
    End If
Next c
If ct = 0 Then
    MsgBox "no rows with month number other than " & mnthNum & " found"
Else
    delRws.EntireRow.Delete
    MsgBox ct & " rows with dates not matching month number " & mnthNum & " were deleted"
End If
End Sub

 

[howard]

Hi JoeMo

Thanks very much for your help. The cose works perfectly

Regards

Howard