Delete rows based on date input
 

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!!!!!

Delete rows based on date input
 

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


wrote in message ...
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!!!!!

Delete rows based on date input
 

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

" wrote:

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!!!!!

Delete rows based on date input
 

Hi Mike and Ron,

Thank you two so much!! :-)

Regards,
B. Li



On Nov 17, 12:14*pm, Mike wrote:
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

" wrote:
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!!!!!