Deleting entire row
 

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.

Deleting entire row
 

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

Deleting entire row
 

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.

Deleting entire row
 

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.

Deleting entire row
 

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.

Deleting entire row
 

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.

Deleting entire row
 

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.