Delete rows based on date input

[bingshuo.li]

Hi,

I am very new to VBA programming and I need help.

I am thinking to write a macro which can -
1. Pop out a window to ask the user to input the beginning date in MM/
DD/YY format.
2. Pop out another window to ask the user to input the ending date in
MM/DD/YY format.
3. Then the macro deletes rows based on the entered date period (all
rows between the beginning date and the ending date will be deleted).

In the target excel worksheet, dates are recorded in column B.

Can anyone help? Thanks!!!!!

 

[Ron de Bruin]

You can use EasyFilter if you want to do this
http://www.rondebruin.nl/easyfilter.htm

 

[Mike]

Sub deleterows()
On Error GoTo ErrHandler
Dim startDate As Date
Dim endDate As Date
Dim rng As Range
Dim i As Long

startDate = InputBox("Enter Start Date as mm/dd/yy format")
endDate = InputBox("Enter EndDate as mm/dd/yy format")

Set rng = ActiveSheet.Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp))

'Work backwards from bottom to top when deleting rows
With rng
For i = .Rows.Count To 1 Step -1
If .Cells(i).Value > startDate And .Cells(i).Value < endDate Then
.Cells(i).EntireRow.delete
End If
Next i
End With
Exit Sub
ErrHandler:
Select Case Err.Number
Case Is = 13
MsgBox "Date is not in proper format"
Case Else
MsgBox "Error #" & Err.Number & vbCrLf & Err.Description
End Select
End Sub

 

[bingshuo.li]

Hi Mike and Ron,

Thank you two so much!!

Regards,
B. Li