deleteing specific rows using a userform

[s_ali_hassan]

'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

 

[Guest]

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

 

[RB Smissaert]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[s_ali_hassan]

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