=now() -30 days
 

I would like to set up my worksheet so that if the date in column C is more
than 30 day ago, that entire row will be deleted. Can anyone help?

Thanks,
Steve

=now() -30 days
 

How strange!

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Column = 3 Then
If Target.Value < Date - 30 Then
Target.EntireRow.Delete
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

"Steve" wrote in message
...
I would like to set up my worksheet so that if the date in column C is
more
than 30 day ago, that entire row will be deleted. Can anyone help?

Thanks,
Steve

=now() -30 days
 

From what I can tell this is set up for when I actually type a date in the
cell. i think I was unclear. Everyday I enter information in this worksheet
that must be saved for 30 days. What I would like it to do is be able to run
a macro that auto deletes all entries older than 30 days. For example, today
is June 1, I would like to auto delete all entries on or before May 2. The
column with the dates is column C.

Thanks again,
Steve

"Bob Phillips" wrote:

How strange!

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Column = 3 Then
If Target.Value < Date - 30 Then
Target.EntireRow.Delete
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

"Steve" wrote in message
...
I would like to set up my worksheet so that if the date in column C is
more
than 30 day ago, that entire row will be deleted. Can anyone help?

Thanks,
Steve

=now() -30 days
 

Sub DeleteRows()
Dim i as Long, lastrow as Long
Dim cell as Range
set lastrow = cells(rows.count,3).End(xlup)
for i = lastrow to 2 step -1
set cell = cells(i,3)
if isdate(cell) then
if Date - cell.Value 30 then
cell.Entirerow.Delete
end if
end if
Next
End Sub


--
Regards,
Tom Ogilvy

"Steve" wrote in message
...
From what I can tell this is set up for when I actually type a date in the
cell. i think I was unclear. Everyday I enter information in this
worksheet
that must be saved for 30 days. What I would like it to do is be able to
run
a macro that auto deletes all entries older than 30 days. For example,
today
is June 1, I would like to auto delete all entries on or before May 2. The
column with the dates is column C.

Thanks again,
Steve

"Bob Phillips" wrote:

How strange!

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Column = 3 Then
If Target.Value < Date - 30 Then
Target.EntireRow.Delete
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

"Steve" wrote in message
...
I would like to set up my worksheet so that if the date in column C is
more
than 30 day ago, that entire row will be deleted. Can anyone help?

Thanks,
Steve

=now() -30 days
 

I am getting an error at:
set lastrow = cells(rows.count,3).End(xlup)

it is a compile error: Object required
"Tom Ogilvy" wrote:




Sub DeleteRows()
Dim i as Long, lastrow as Long
Dim cell as Range
set lastrow = cells(rows.count,3).End(xlup)
for i = lastrow to 2 step -1
set cell = cells(i,3)
if isdate(cell) then
if Date - cell.Value 30 then
cell.Entirerow.Delete
end if
end if
Next
End Sub


--
Regards,
Tom Ogilvy

"Steve" wrote in message
...
From what I can tell this is set up for when I actually type a date in the
cell. i think I was unclear. Everyday I enter information in this
worksheet
that must be saved for 30 days. What I would like it to do is be able to
run
a macro that auto deletes all entries older than 30 days. For example,
today
is June 1, I would like to auto delete all entries on or before May 2. The
column with the dates is column C.

Thanks again,
Steve

"Bob Phillips" wrote:

How strange!

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Column = 3 Then
If Target.Value < Date - 30 Then
Target.EntireRow.Delete
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

"Steve" wrote in message
...
I would like to set up my worksheet so that if the date in column C is
more
than 30 day ago, that entire row will be deleted. Can anyone help?

Thanks,
Steve

=now() -30 days
 

Try

Sub DeleteRows()
Dim i as Long, lastrow as Long
Dim cell as Range
lastrow = cells(rows.count,3).End(xlup).Row
for i = lastrow to 2 step -1
set cell = cells(i,3)
if isdate(cell) then
if Date - cell.Value 30 then
cell.Entirerow.Delete
end if
end if
Next
End Sub


--
HTH

Bob Phillips

"Steve" wrote in message
...
I am getting an error at:
set lastrow = cells(rows.count,3).End(xlup)

it is a compile error: Object required
"Tom Ogilvy" wrote:




Sub DeleteRows()
Dim i as Long, lastrow as Long
Dim cell as Range
set lastrow = cells(rows.count,3).End(xlup)
for i = lastrow to 2 step -1
set cell = cells(i,3)
if isdate(cell) then
if Date - cell.Value 30 then
cell.Entirerow.Delete
end if
end if
Next
End Sub


--
Regards,
Tom Ogilvy

"Steve" wrote in message
...
From what I can tell this is set up for when I actually type a date in
the
cell. i think I was unclear. Everyday I enter information in this
worksheet
that must be saved for 30 days. What I would like it to do is be able
to
run
a macro that auto deletes all entries older than 30 days. For example,
today
is June 1, I would like to auto delete all entries on or before May 2.
The
column with the dates is column C.

Thanks again,
Steve

"Bob Phillips" wrote:

How strange!

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Column = 3 Then
If Target.Value < Date - 30 Then
Target.EntireRow.Delete
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

"Steve" wrote in message
...
I would like to set up my worksheet so that if the date in column
C is
more
than 30 day ago, that entire row will be deleted. Can anyone help?

Thanks,
Steve

=now() -30 days
 

Thanks for the fix Bob. I seem to make that exact typo too often.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
Try

Sub DeleteRows()
Dim i as Long, lastrow as Long
Dim cell as Range
lastrow = cells(rows.count,3).End(xlup).Row
for i = lastrow to 2 step -1
set cell = cells(i,3)
if isdate(cell) then
if Date - cell.Value 30 then
cell.Entirerow.Delete
end if
end if
Next
End Sub


--
HTH

Bob Phillips

"Steve" wrote in message
...
I am getting an error at:
set lastrow = cells(rows.count,3).End(xlup)

it is a compile error: Object required
"Tom Ogilvy" wrote:




Sub DeleteRows()
Dim i as Long, lastrow as Long
Dim cell as Range
set lastrow = cells(rows.count,3).End(xlup)
for i = lastrow to 2 step -1
set cell = cells(i,3)
if isdate(cell) then
if Date - cell.Value 30 then
cell.Entirerow.Delete
end if
end if
Next
End Sub


--
Regards,
Tom Ogilvy

"Steve" wrote in message
...
From what I can tell this is set up for when I actually type a date
in
the
cell. i think I was unclear. Everyday I enter information in this
worksheet
that must be saved for 30 days. What I would like it to do is be
able
to
run
a macro that auto deletes all entries older than 30 days. For
example,
today
is June 1, I would like to auto delete all entries on or before May
2.
The
column with the dates is column C.

Thanks again,
Steve

"Bob Phillips" wrote:

How strange!

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Column = 3 Then
If Target.Value < Date - 30 Then
Target.EntireRow.Delete
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

"Steve" wrote in message
...
I would like to set up my worksheet so that if the date in
column
C is
more
than 30 day ago, that entire row will be deleted. Can anyone
help?

Thanks,
Steve

=now() -30 days
 

My pleasure Tom.

Regards

Bob

"Tom Ogilvy" wrote in message
...
Thanks for the fix Bob. I seem to make that exact typo too often.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
Try

Sub DeleteRows()
Dim i as Long, lastrow as Long
Dim cell as Range
lastrow = cells(rows.count,3).End(xlup).Row
for i = lastrow to 2 step -1
set cell = cells(i,3)
if isdate(cell) then
if Date - cell.Value 30 then
cell.Entirerow.Delete
end if
end if
Next
End Sub


--
HTH

Bob Phillips

"Steve" wrote in message
...
I am getting an error at:
set lastrow = cells(rows.count,3).End(xlup)

it is a compile error: Object required
"Tom Ogilvy" wrote:




Sub DeleteRows()
Dim i as Long, lastrow as Long
Dim cell as Range
set lastrow = cells(rows.count,3).End(xlup)
for i = lastrow to 2 step -1
set cell = cells(i,3)
if isdate(cell) then
if Date - cell.Value 30 then
cell.Entirerow.Delete
end if
end if
Next
End Sub


--
Regards,
Tom Ogilvy

"Steve" wrote in message
...
From what I can tell this is set up for when I actually type a
date
in
the
cell. i think I was unclear. Everyday I enter information in this
worksheet
that must be saved for 30 days. What I would like it to do is be
able
to
run
a macro that auto deletes all entries older than 30 days. For
example,
today
is June 1, I would like to auto delete all entries on or before
May
2.
The
column with the dates is column C.

Thanks again,
Steve

"Bob Phillips" wrote:

How strange!

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Column = 3 Then
If Target.Value < Date - 30 Then
Target.EntireRow.Delete
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

"Steve" wrote in message
...
I would like to set up my worksheet so that if the date in
column
C is
more
than 30 day ago, that entire row will be deleted. Can anyone
help?

Thanks,
Steve