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

**rain4u** · 04-25-2011, 07:58 PM

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

**nimrod** · 04-25-2011, 08:12 PM

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

**rain4u** · 04-25-2011, 08:17 PM

Yes I forgot to mention these details. Active sheet, column 9 ( or column col i) and it does have header row (row1).

Cheers
Rain

**rain4u** · 04-27-2011, 12:33 AM

*bump*bump*

**nimrod** · 04-27-2011, 11:25 AM

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

**rain4u** · 04-30-2011, 04:10 AM

Thx Nimrod. It works. I also learned from your code. If someone else needs to get the same result but get it by using autofilters then the following would do the job

Sub keep_dates()
    Dim dbStart As Double, dbEnd As Double
    Application.ScreenUpdating = 0
   dbStart = Range("figures_date_Monday").Value
   dbEnd = Range("figures_date_Friday").Value

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

Cheers
Rain

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions