'ive got a userform into which the user enters a particular date.
'The code then looks at each cell to see if the date in the cell is
'greater than the date entered by the user. If it is, then the row is
deleted
'if not then look at the next cell down.
'stop when nothing is in the cell.
'the problem i have is that, say i enter the date 12/05/2006, then run
the program,
'the dates after 12/05/2006 get deleted (which is correct), but it also
deletes
'12/05/2006 rows aswell (which is not right).
'i cant work out whats wrong, or any other way of doing it. Any help
please? much appreciated.

code:

Private Sub CommandButton2_Click()

Dim date2 As Date

date2 = TextBox2.Text

Range("F9").Select

Do Until ActiveCell.Value = ""

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

Loop

End Sub


--
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=555542

Be more explicit

Dim dtCellValue as date

dtCellValue = cdate(activecell.value)

then run a datediff on the dtCellValue and date2 and run the delete on that

Also, better not to use activecell - reference the cell explicitly ie

dim oActiveCell as Excel.range

set oActiveCell = thisworkbook.worksheets(sheet_name_here).raneg("f9 ")

and then use oActiveCell rather than ActiveCell


have a go with that and post back if you get stuck




--
www.alignment-systems.com


"s_ali_hassan" wrote:


'ive got a userform into which the user enters a particular date.
'The code then looks at each cell to see if the date in the cell is
'greater than the date entered by the user. If it is, then the row is
deleted
'if not then look at the next cell down.
'stop when nothing is in the cell.
'the problem i have is that, say i enter the date 12/05/2006, then run
the program,
'the dates after 12/05/2006 get deleted (which is correct), but it also
deletes
'12/05/2006 rows aswell (which is not right).
'i cant work out whats wrong, or any other way of doing it. Any help
please? much appreciated.

code:

Private Sub CommandButton2_Click()

Dim date2 As Date

date2 = TextBox2.Text

Range("F9").Select

Do Until ActiveCell.Value = ""

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

Loop

End Sub


--
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=555542

Try something like this:

Sub test()

Dim i As Long
Dim daDate As Date

daDate = "15/05/2006"

For i = 10 To 1 Step -1
If Val(Cells(i, 1)) Val(daDate) Then
Rows(i).Delete
End If
Next

End Sub


RBS


"s_ali_hassan"
wrote in message
news:s_ali_hassan.2a0f50_1151325909.0764@excelforu m-nospam.com...

'ive got a userform into which the user enters a particular date.
'The code then looks at each cell to see if the date in the cell is
'greater than the date entered by the user. If it is, then the row is
deleted
'if not then look at the next cell down.
'stop when nothing is in the cell.
'the problem i have is that, say i enter the date 12/05/2006, then run
the program,
'the dates after 12/05/2006 get deleted (which is correct), but it also
deletes
'12/05/2006 rows aswell (which is not right).
'i cant work out whats wrong, or any other way of doing it. Any help
please? much appreciated.

code:

Private Sub CommandButton2_Click()

Dim date2 As Date

date2 = TextBox2.Text

Range("F9").Select

Do Until ActiveCell.Value = ""

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

Loop

End Sub


--
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=555542

tried that way (john.greenan), but nothing seems to happen in the
macro.
ive probably done it wrong!
any other ideas or help??


--
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=555542