for..next statement problem

[s_ali_hassan]

I`m having a bit of a problem with a for...next loop.
In the worksheet ive got a column which has a list of dates in.
What i need to do is for a form to pop-up and ask the user to enter from which dates they would like the info to and from, then the other dates that are not in the range will all be deleted (the whole rows wiill be deleted) will all be deleted.

my code is as follows:
Private Sub CommandButton1_Click()

Dim date1 As Date

date1 = TextBox1.Text

Range("F9").Select 'select the first cell with the date in

For Each cell In ActiveCell.CurrentRegion.Cells

If ActiveCell = "" Then CommandButton2_Click
If ActiveCell < date1 Then ActiveCell.EntireRow.Delete
If ActiveCell > date1 Then ActiveCell.Offset(1, 0).Activate

Next

End Sub

Private Sub CommandButton2_Click()

Dim date2 As Date

date2 = TextBox2.Text

Range("F9").Select

For Each cell In ActiveCell.CurrentRegion.Cells

If ActiveCell > date2 Then ActiveCell.EntireRow.Delete
If ActiveCell < date2 Then ActiveCell.Offset(1, 0).Activate

Next

End Sub

What happens is, it just gets stuck into a continuous loop, and ive tried loads of things, but still unable to make the loop somehow stop.

pls anyone help me on this! any help appreciated.

thanks

[Bob Phillips]

Just one button

Private Sub CommandButton1_Click()
Dim date1 As Date
Dim date2 As Date
Dim rng As Range

date1 = TextBox1.Text
date2 = TextBox2.Text

For Each cell In Range("F9").CurrentRegion.Cells

If cell < date1 Or cell > date2 Then
If rng Is Nothing Then
Set rng = cell
Else
Set rng = Union(rng, cell)
End If
End If

Next cell

If Not rng Is Nothing Then rng.EntireRow.Delete

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"s_ali_hassan" <s_ali_hassan.29ebzz_1150295403.5044@excelforum-nospam.com>
wrote in message
news:s_ali_hassan.29ebzz_1150295403.5044@excelforum-nospam.com...
>
> I`m having a bit of a problem with a for...next loop.
> In the worksheet ive got a column which has a list of dates in.
> What i need to do is for a form to pop-up and ask the user to enter
> from which dates they would like the info to and from, then the other
> dates that are not in the range will all be deleted (the whole rows
> wiill be deleted) will all be deleted.
>
> my code is as follows:
> Private Sub CommandButton1_Click()
>
> Dim date1 As Date
>
> date1 = TextBox1.Text
>
> Range("F9").Select 'select the first cell with the date in
>
> For Each cell In ActiveCell.CurrentRegion.Cells
>
> If ActiveCell = "" Then CommandButton2_Click
> If ActiveCell < date1 Then ActiveCell.EntireRow.Delete
> If ActiveCell > date1 Then ActiveCell.Offset(1, 0).Activate
>
> Next
>
> End Sub
>
> Private Sub CommandButton2_Click()
>
> Dim date2 As Date
>
> date2 = TextBox2.Text
>
> Range("F9").Select
>
> For Each cell In ActiveCell.CurrentRegion.Cells
>
> If ActiveCell > date2 Then ActiveCell.EntireRow.Delete
> If ActiveCell < date2 Then ActiveCell.Offset(1, 0).Activate
>
> Next
>
> End Sub
>
> What happens is, it just gets stuck into a continuous loop, and ive
> tried loads of things, but still unable to make the loop somehow stop.
>
> pls anyone help me on this! any help appreciated.
>
> thanks
>
>
> --
> s_ali_hassan
> ------------------------------------------------------------------------
> s_ali_hassan's Profile:
http://www.excelforum.com/member.php...o&userid=35325
> View this thread: http://www.excelforum.com/showthread...hreadid=551870
>