+ Reply to Thread
Results 1 to 5 of 5

Delete rows based on date range entered?

  1. #1
    Registered User
    Join Date
    11-28-2006
    Posts
    4

    Delete rows based on date range entered?

    Newbie here. I created a user form for entering orders. Now I need some way to delete old orders (rows) in the database based on a date range (in a column) that the user can enter.

    I found this macro that works for a single criteria in every column (Name, Book, Qty, Language) but it doesn't work for the Date column. And I can't figure out why. This macro may not be exactly what I'm looking for since I need multiple critieria(right?), but it's getting me closer to the goal.


    Sub DeleteRowsFastest()
    Sheets("Data Entry").Select
    Dim rTable As Range
    Dim lCol As Long
    Dim vCriteria


    On Error Resume Next
    'Determine the table range
    With Selection
    If .Cells.Count > 1 Then
    Set rTable = Selection
    Else

    Set rTable = .CurrentRegion
    On Error GoTo 0
    End If
    End With

    'Determine if table range is valid
    If rTable Is Nothing Or rTable.Cells.Count = 1 Or WorksheetFunction.CountA(rTable) < 2 Then
    MsgBox "Could not determine you table range.", vbCritical, "Ozgrid.com"
    Exit Sub
    End If

    'Get the criteria in the form of text or number.
    vCriteria = Application.InputBox(Prompt:="Type in the criteria that macthing rows should be deleted. " _
    & "If the criteria is in a cell, point to the cell with your mouse pointer", _
    Title:="CONDITIONAL ROW DELETION CRITERIA", Type:=1 + 2)

    'Go no further if they Cancel.
    If vCriteria = "False" Then Exit Sub

    'Get the relative column number where the criteria should be found
    lCol = Application.InputBox(Prompt:="Type in the relative number of the column where " _
    & "the criteria can be found.", Title:="CONDITIONAL ROW DELETION COLUMN NUMBER", Type:=1)

    'Cancelled
    If lCol = 0 Then Exit Sub

    'Remove any existing AutoFilters
    ActiveSheet.AutoFilterMode = False

    'Filter table based on vCriteria using the relative column position stored in lCol.
    rTable.AutoFilter Field:=lCol, Criteria1:=vCriteria

    'Delete all rows that are NOT hidden by AutoFilter.
    rTable.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

    'Remove AutoFilters
    ActiveSheet.AutoFilterMode = False
    On Error GoTo 0
    End Sub

    Any help would be greatly appreciated.

    Thanks,
    Diva

  2. #2
    Registered User
    Join Date
    11-28-2006
    Posts
    4
    Bueller? Bueller?


  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,475
    Quote Originally Posted by fzrdiva
    Bueller? Bueller?

    Pardon??.....

  4. #4
    Registered User
    Join Date
    11-28-2006
    Posts
    4
    It's a quote from the movie Ferris Bueller...

    I'm using it in the sense of "Anyone know how to help me?"


  5. #5
    Registered User
    Join Date
    11-28-2006
    Posts
    4
    Well, I figured most of it out by taking bits and pieces of other macros, but it also deletes the Header Row. Can anyone see why this would happen or what I need to do different?

    'Get the criteria in the form of text or number.
    vCriteria = Application.InputBox(Prompt:="Delete orders on or before this date: ", _
    Title:="DATE", Type:=1 + 2)
    '
    Sheets("Data Entry").Select
    Range("E1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=5, Criteria1:="<=" & vCriteria, Operator:=xlAnd

    'Delete all rows that are NOT hidden by AutoFilter.
    Selection.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

    'Remove AutoFilters
    ActiveSheet.AutoFilterMode = False

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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