Results 1 to 6 of 6

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

Threaded View

rain4u Macro, autofilter, delete... 04-25-2011, 07:58 PM
nimrod Re: Macro, autofilter, delete... 04-25-2011, 08:12 PM
rain4u Re: Macro, autofilter, delete... 04-25-2011, 08:17 PM
rain4u Re: Macro, autofilter, delete... 04-27-2011, 12:33 AM
nimrod Re: Macro, autofilter, delete... 04-27-2011, 11:25 AM
rain4u Re: Macro, autofilter, delete... 04-30-2011, 04:10 AM
  1. #1
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    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
    Last edited by rain4u; 04-30-2011 at 04:11 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1