Thread: Macro, autofilter, delete rows outside date range, defined names as criteria

Hi. I have two named cells that contain dates:

figures_date_Monday
figures_date_Friday

I would like to have a macro using auto filter to delete rows that do not fall between those dates. Monday as start date and Friday as end date (both inclusive). Maybe this code below that I found on the Internet can be adjusted to do this task:

Sub keep_dates()
    Dim stStart As String, stEnd As String
    Dim dbStart As Double, dbEnd As Double

    Application.ScreenUpdating = 0

    stStart = InputBox("Please supply a start date", "Date Input", Date)
    stEnd = InputBox("Please supply an end date", "Date Input", Date)

    If Not IsDate(stStart) Or Not IsDate(stEnd) Then
        MsgBox "Invalid Dates", vbExclamation, "Input Error"
        GoTo ExitSub
    End If

    dbStart = CDbl(CDate(stStart))
    dbEnd = CDbl(CDate(stEnd))

    With ActiveSheet.Columns(9)
        .AutoFilter Field:=1, Criteria1:="<" & dbStart, Operator:=xlOr, Criteria2:=">" & dbEnd
        .Resize(Rows.Count - 1).Offset(1).SpecialCells(12).EntireRow.Delete
        If .Parent.AutoFilterMode = True Then .AutoFilter
    End With
    
ExitSub:
    Application.ScreenUpdating = 1
End Sub

This code above prompts with input window box to allow user to manually write the dates but remember I would like to use named cells instead.


Any ideas

Cheers
Rain

What column are the dates in ? Is the sheet the activesheet ? Do you have a header row that should be excluded from inspection ?

Assumptions:
-- All values in Column "i' , except for row 1, are dates

Public Sub DeleteRowByDateRange()
   Dim StartDate As Date, EndDate As Date
   
   'Config Here
   Const DateColumn As String = "I"
   
   StartDate = Range("figures_date_Monday").Value
   EndDate = Range("figures_date_Friday").Value

   For iRow = Cells(Rows.Count, DateColumn).End(xlUp).Row + 1 To 2 Step -1
      If Cells(iRow, DateColumn).Value < StartDate Or Cells(iRow, DateColumn).Value > EndDate Then
         Cells(iRow, DateColumn).EntireRow.Delete
      End If
      
   Next iRow

End Sub