Thread: Macro to delete dates > xx date

I have a report that has various tasks along the top and various sites along the site. The data in the table are forecast dates where they have dates.

I'd like to have the same report but only show dates less than a certain date (for example to show past dates or dates due this month, etc.). I'd like to have the macro show an input box where I can enter the date. But is it possible for the macro to then delete any dates that are greater than the entered date?

I know I can use conditional formatting to 'hide' the dates but I'd prefer them to be deleted instead - as it would help with my next task.

I fear this might need some sort of loop that would slow the macro down considerable?

koltregaskes,

Something like this?

Sub DeleteUnwantedRowsMacro_for_koltregaskes()
    
    Const DateCol As String = "C"
    Const HeaderRow As Long = 1
    
    Dim UserInput As String
    UserInput = InputBox(Title:="Filter Date", _
                         Prompt:="Enter a date." & Chr(10) & _
                                 "All items after the entered date will be deleted.", _
                         Default:=Date)
    
    Dim ChosenDate As Date
    On Error GoTo InvalidDate
    ChosenDate = UserInput
    
    Dim rngDates As Range: Set rngDates = Range(DateCol & HeaderRow, Cells(Rows.Count, DateCol).End(xlUp))
    rngDates.AutoFilter Field:=1, Criteria1:=">" & ChosenDate
    Set rngDates = rngDates.Offset(1, 0).SpecialCells(xlCellTypeVisible)
    rngDates.EntireRow.Delete xlShiftUp
    Range(DateCol & 1).AutoFilter
    Exit Sub
    
InvalidDate:
    MsgBox UserInput & " is not a valid date.  Exiting macro."
    Exit Sub
End Sub

Hope that helps,
~tigeravatar

Ta, so how exactly does this work? :-)