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 <daisy2...@discussions.microsoft.com>
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" <(E-Mail Removed)> wrote in message
news:39E49D7F-CED4-456D-8853-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:39E49D7F-CED4-456D-8853-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
news:ED091C46-0E9D-4730-801A-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:39E49D7F-CED4-456D-8853-(E-Mail Removed)...
>> >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" <(E-Mail Removed)> wrote in message
> news:ED091C46-0E9D-4730-801A-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:39E49D7F-CED4-456D-8853-(E-Mail Removed)...
> >> >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" <(E-Mail Removed)> wrote in message
news:3C8F901B-71BF-49EB-BB76-(E-Mail Removed)...
>
>
> 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" <(E-Mail Removed)> wrote in message
>> news:ED091C46-0E9D-4730-801A-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:39E49D7F-CED4-456D-8853-(E-Mail Removed)...
>> >> >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.
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>