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
Bookmarks