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

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


<(E-Mail Removed)> wrote in message news:e6ca0fb1-8fb5-4c0e-8061-(E-Mail Removed)...
> 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!!!!!
>

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

"(E-Mail Removed)" 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!!!!!
>
>

Hi Mike and Ron,

Thank you two so much!! :-)

Regards,
B. Li



On Nov 17, 12:14*pm, Mike <M...@discussions.microsoft.com> 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
>
> "bingshuo...@gmail.com" 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!!!!!