I need an entire row deleted if column 2 on worksheet 3; date is one year
later but is less then todays date. I need this to run when my workbook is
opened.

Ex
B4 January 2, 2008
B5 November 4, 2007
B6 December 26, 2007
B7 December 5, 2007


Row 5 and row 7 are deleted when I open the workbook.

On Dec 8, 3:49*pm, daisy2008
wrote:
I need an entire row deleted if column 2 on worksheet 3; date is one year
later but is less then todays date. *I need this to run when my workbook is
opened.

Ex
B4 January 2, 2008
B5 November 4, 2007
B6 December 26, 2007
B7 December 5, 2007

Row 5 *and row 7 are deleted when I open the workbook.

Well the opening the notebook is up to you. However, the code will be
similar to...

for i = 1 to ASMANYROWSASYOUCAREABOUT
theCell = Cells(2,i)
if abs(datediff("yyyy", theCell, date)) 1 then
theCell.entirerow.delete
end if
next i

Maury

Daisy
Place this macro in the workbook module of your file. HTH Otto
Private Sub Workbook_Open()
Dim rColB As Range
Dim c As Long
With Sheets("3")
Set rColB = .Range("B4", .Range("B" & Rows.Count).End(xlUp))
For c = rColB.Count To 1 Step -1
If DateSerial(Year(Date), Month(rColB(c)), Day(rColB(c))) < Date
And _
Year(rColB(c)) + 1 = Year(Date) Then _
rColB(c).EntireRow.Delete
Next c
End With
End Sub
"daisy2008" wrote in message
...
I need an entire row deleted if column 2 on worksheet 3; date is one year
later but is less then todays date. I need this to run when my workbook
is
opened.

Ex
B4 January 2, 2008
B5 November 4, 2007
B6 December 26, 2007
B7 December 5, 2007


Row 5 and row 7 are deleted when I open the workbook.

Thank you Otto

I'm getting a compile error:syntax error and then it highlighted

If Dateserial........

What am I doing wrong?
I have 1000 rows it will need to look through.

"Otto Moehrbach" wrote:

Daisy
Place this macro in the workbook module of your file. HTH Otto
Private Sub Workbook_Open()
Dim rColB As Range
Dim c As Long
With Sheets("3")
Set rColB = .Range("B4", .Range("B" & Rows.Count).End(xlUp))
For c = rColB.Count To 1 Step -1
If DateSerial(Year(Date), Month(rColB(c)), Day(rColB(c))) < Date
And _
Year(rColB(c)) + 1 = Year(Date) Then _
rColB(c).EntireRow.Delete
Next c
End With
End Sub
"daisy2008" wrote in message
...
I need an entire row deleted if column 2 on worksheet 3; date is one year
later but is less then todays date. I need this to run when my workbook
is
opened.

Ex
B4 January 2, 2008
B5 November 4, 2007
B6 December 26, 2007
B7 December 5, 2007


Row 5 and row 7 are deleted when I open the workbook.

Daisy
You are probably the victim of line wrapping. This is always a problem
when code (a macro) is sent to someone in an email or a post. Excel is very
unforgiving with line wrapping. The DateSerial line is 3 lines to you and
me but is taken as one line by Excel. For this to work, the underline (_)
character must be the last character in the line. Rearrange that line so
that it is 3 lines with the underline character at the end of the first 2
lines. Try that. HTH Otto
"daisy2008" wrote in message
...
Thank you Otto

I'm getting a compile error:syntax error and then it highlighted

If Dateserial........

What am I doing wrong?
I have 1000 rows it will need to look through.

"Otto Moehrbach" wrote:

Daisy
Place this macro in the workbook module of your file. HTH Otto
Private Sub Workbook_Open()
Dim rColB As Range
Dim c As Long
With Sheets("3")
Set rColB = .Range("B4", .Range("B" & Rows.Count).End(xlUp))
For c = rColB.Count To 1 Step -1
If DateSerial(Year(Date), Month(rColB(c)), Day(rColB(c))) <
Date
And _
Year(rColB(c)) + 1 = Year(Date) Then _
rColB(c).EntireRow.Delete
Next c
End With
End Sub
"daisy2008" wrote in message
...
I need an entire row deleted if column 2 on worksheet 3; date is one
year
later but is less then todays date. I need this to run when my
workbook
is
opened.

Ex
B4 January 2, 2008
B5 November 4, 2007
B6 December 26, 2007
B7 December 5, 2007


Row 5 and row 7 are deleted when I open the workbook.

Otto, you are so great!!!!!

Thank you so very much.
Daisy




"Otto Moehrbach" wrote:

Daisy
You are probably the victim of line wrapping. This is always a problem
when code (a macro) is sent to someone in an email or a post. Excel is very
unforgiving with line wrapping. The DateSerial line is 3 lines to you and
me but is taken as one line by Excel. For this to work, the underline (_)
character must be the last character in the line. Rearrange that line so
that it is 3 lines with the underline character at the end of the first 2
lines. Try that. HTH Otto
"daisy2008" wrote in message
...
Thank you Otto

I'm getting a compile error:syntax error and then it highlighted

If Dateserial........

What am I doing wrong?
I have 1000 rows it will need to look through.

"Otto Moehrbach" wrote:

Daisy
Place this macro in the workbook module of your file. HTH Otto
Private Sub Workbook_Open()
Dim rColB As Range
Dim c As Long
With Sheets("3")
Set rColB = .Range("B4", .Range("B" & Rows.Count).End(xlUp))
For c = rColB.Count To 1 Step -1
If DateSerial(Year(Date), Month(rColB(c)), Day(rColB(c))) <
Date
And _
Year(rColB(c)) + 1 = Year(Date) Then _
rColB(c).EntireRow.Delete
Next c
End With
End Sub
"daisy2008" wrote in message
...
I need an entire row deleted if column 2 on worksheet 3; date is one
year
later but is less then todays date. I need this to run when my
workbook
is
opened.

Ex
B4 January 2, 2008
B5 November 4, 2007
B6 December 26, 2007
B7 December 5, 2007


Row 5 and row 7 are deleted when I open the workbook.

You're welcome. Thanks for the feedback. Otto
"daisy2008" wrote in message
...


Otto, you are so great!!!!!

Thank you so very much.
Daisy




"Otto Moehrbach" wrote:

Daisy
You are probably the victim of line wrapping. This is always a
problem
when code (a macro) is sent to someone in an email or a post. Excel is
very
unforgiving with line wrapping. The DateSerial line is 3 lines to you
and
me but is taken as one line by Excel. For this to work, the underline
(_)
character must be the last character in the line. Rearrange that line so
that it is 3 lines with the underline character at the end of the first 2
lines. Try that. HTH Otto
"daisy2008" wrote in message
...
Thank you Otto

I'm getting a compile error:syntax error and then it highlighted

If Dateserial........

What am I doing wrong?
I have 1000 rows it will need to look through.

"Otto Moehrbach" wrote:

Daisy
Place this macro in the workbook module of your file. HTH Otto
Private Sub Workbook_Open()
Dim rColB As Range
Dim c As Long
With Sheets("3")
Set rColB = .Range("B4", .Range("B" & Rows.Count).End(xlUp))
For c = rColB.Count To 1 Step -1
If DateSerial(Year(Date), Month(rColB(c)), Day(rColB(c)))
<
Date
And _
Year(rColB(c)) + 1 = Year(Date) Then _
rColB(c).EntireRow.Delete
Next c
End With
End Sub
"daisy2008" wrote in message
...
I need an entire row deleted if column 2 on worksheet 3; date is one
year
later but is less then todays date. I need this to run when my
workbook
is
opened.

Ex
B4 January 2, 2008
B5 November 4, 2007
B6 December 26, 2007
B7 December 5, 2007


Row 5 and row 7 are deleted when I open the workbook.