Deleting entire row

[daisy2008]

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.

 

[Maury Markowitz]

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

 

[Otto Moehrbach]

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]

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]

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]

Otto, you are so great!!!!!

Thank you so very much.
Daisy

 

[Otto Moehrbach]

You're welcome. Thanks for the feedback. Otto

Otto, you are so great!!!!!

Thank you so very much.
Daisy