OK, this is my last question I promise and it's not that har

[buntykins]

In column I of a sheet I am working on I have lists of dates. What I need to happen is that if the date is more than 5 days old, the entire row it is on is deleted

Does anyone have any ideas? I'm sure I've seen something like it before

Janie

[Tom Schreiner]

Hi
Right click on your sheet tab,
choose view code,
place this in there...

Whenever you open your sheet,
this code will be run.

Private Sub Worksheet_Activate()

Dim c

For Each c In ActiveSheet.UsedRange

    If c.Column = 1 And IsDate(c) = True And _

      Now > c + 5 Then Rows(c.Row).EntireRow.Delete

Next

End Sub

Tom

[ This Message was edited by: TsTom on 2002-04-26 01:54 ]

[buntykins]

That's great Tom, thanks, but is there any way of doing it every time the actual workbook is opened? The sheet is called "Data" but it's not actually accessible to the users so they won't be able to open it

Janie

[buntykins]

Hi there, I've managed to get this code to work during the workbook open event. It's great except that I was wrong, it's not column I, it's column J. Do I need to change the reference to J or to 10 as that's the column number?

Janie

[buntykins]

Now it's saying Type Mismatch????

[Tom Schreiner]

Private Sub Workbook_Open()

Dim c

For Each c In ActiveSheet.UsedRange

    If c.Column = 10 And IsDate(c) = True And _

      Now > c + 5 Then Rows(c.Row).EntireRow.Delete

Next

End Sub

You were correct...
You need to replace ActiveSheet
as well.

Replace

ActiveSheet.UsedRange

with

Sheets("YourSheetName").UsedRange

Place the name of your sheet
between the quotes...

Tom

[buntykins]

I've tried all ways now, and all I get is Type Mismatch???

This is the code:

Private Sub Workbook_Open()
Dim c
For Each c In Sheets("Sheet2").UsedRange
If c.Column = 10 And IsDate(c) = True And _
Now > c + 5 Then Rows(c.Row).EntireRow.Delete
Next
End Sub


Janie

[ This Message was edited by: buntykins on 2002-04-26 02:48 ]

[Tom Schreiner]

Private Sub Workbook_Open()

Dim c

With Sheets("Data")

    For Each c In .UsedRange

        If c.Column = 10 And IsDate(c) = True And _

          Now > c + 5 Then .Rows(c.Row).EntireRow.Delete

    Next

End With

End Sub

I could not duplicate your error...
It appears that you did not change the sheet name. If your sheet name is "Data" then this should work...
Try this
Tom

[ This Message was edited by: TsTom on 2002-04-26 02:52 ]

[buntykins]

This is crazy, it still won't work??? I get runtime error 13 Type Mismatch all the time and it highlights this part:

If c.Column = 10 And IsDate(c) = True And _
Now > c + 5 Then .Rows(c.Row).EntireRow.Delete

[Tom Schreiner]

If c.Column = 10 And IsDate(c) = True And _
Now > c + 5 Then .Rows(c.Row).EntireRow.Delete


No error on my machine...

Gotta go...

Replace the above with this...

If c.Column = 10 And IsDate(c.value) = True And _
Now > c.value + 5 Then .Rows(c.Row).EntireRow.Delete

Tom