I have a spreadsheet with birthdates listed in column H. Is it possible to
have certain rows automatically deleted when they hit 40 years old (or older)?

Hi,

It's with some personal pain that I'm to be deleted because I'm over 40 (in
fact twice that) thatI offer you this solution to consider. Right click the
appropriate sheet tab, view code and paste this in on the right. Every time
you select the sheet those poor souls older than 40 are deleted.

Private Sub Worksheet_Activate()
Dim MyRange, MyRange1 As Range
lastrow = Cells(Rows.Count, "H").End(xlUp).Row
Set MyRange = Range("H1:H" & lastrow)
For Each c In MyRange
If IsDate(c.Value) And Int((Date - c.Value) / 365.25) 40 Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Select
End If
Selection.Delete
Range("A1").select
End Sub

Mike

"Go Terps" wrote:

I have a spreadsheet with birthdates listed in column H. Is it possible to
have certain rows automatically deleted when they hit 40 years old (or older)?

You beat me to it. I wasn't sure if I wanted to respond because I'm a
Yellow Jacket in Terps country.

:)

Barb Reinhardt



"Mike H" wrote:

Hi,

It's with some personal pain that I'm to be deleted because I'm over 40 (in
fact twice that) thatI offer you this solution to consider. Right click the
appropriate sheet tab, view code and paste this in on the right. Every time
you select the sheet those poor souls older than 40 are deleted.

Private Sub Worksheet_Activate()
Dim MyRange, MyRange1 As Range
lastrow = Cells(Rows.Count, "H").End(xlUp).Row
Set MyRange = Range("H1:H" & lastrow)
For Each c In MyRange
If IsDate(c.Value) And Int((Date - c.Value) / 365.25) 40 Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Select
End If
Selection.Delete
Range("A1").select
End Sub

Mike

"Go Terps" wrote:

I have a spreadsheet with birthdates listed in column H. Is it possible to
have certain rows automatically deleted when they hit 40 years old (or older)?

I'm now going to sit in a darkened room and contemplate the benefits of age,
now where shall I begin:-

No.1......errrrm hang on I'll think of one.

Mike

"Barb Reinhardt" wrote:

You beat me to it. I wasn't sure if I wanted to respond because I'm a
Yellow Jacket in Terps country.

:)

Barb Reinhardt



"Mike H" wrote:

Hi,

It's with some personal pain that I'm to be deleted because I'm over 40 (in
fact twice that) thatI offer you this solution to consider. Right click the
appropriate sheet tab, view code and paste this in on the right. Every time
you select the sheet those poor souls older than 40 are deleted.

Private Sub Worksheet_Activate()
Dim MyRange, MyRange1 As Range
lastrow = Cells(Rows.Count, "H").End(xlUp).Row
Set MyRange = Range("H1:H" & lastrow)
For Each c In MyRange
If IsDate(c.Value) And Int((Date - c.Value) / 365.25) 40 Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Select
End If
Selection.Delete
Range("A1").select
End Sub

Mike

"Go Terps" wrote:

I have a spreadsheet with birthdates listed in column H. Is it possible to
have certain rows automatically deleted when they hit 40 years old (or older)?

Mike,

Sorry for the discrimination, but rules are rules!

I appreciate all your help, but the solution did not work. It deletes only
the top value of column A and keeps every other column the exact same. I
would like the entire row deleted if they were born more than 40 years ago I
have Excel 2003 and the spreadsheet is set up as follows with headers:

Column A - Name
Column B - Address
Column C - Line 2 address
Column D - College
Column E - City
Column F - State code
Column G - Zip Code
Column H - Birthday in xx/xx/xxxx format
Column I - Birthday #2 (If two people)

"Mike H" wrote:

Hi,

It's with some personal pain that I'm to be deleted because I'm over 40 (in
fact twice that) thatI offer you this solution to consider. Right click the
appropriate sheet tab, view code and paste this in on the right. Every time
you select the sheet those poor souls older than 40 are deleted.

Private Sub Worksheet_Activate()
Dim MyRange, MyRange1 As Range
lastrow = Cells(Rows.Count, "H").End(xlUp).Row
Set MyRange = Range("H1:H" & lastrow)
For Each c In MyRange
If IsDate(c.Value) And Int((Date - c.Value) / 365.25) 40 Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Select
End If
Selection.Delete
Range("A1").select
End Sub

Mike

"Go Terps" wrote:

I have a spreadsheet with birthdates listed in column H. Is it possible to
have certain rows automatically deleted when they hit 40 years old (or older)?

I find that difficult to explain because i've tested it again and it works
perfectly with properly formatted dates in Column H which I suspect may be
your problem. I suggest you check the dates are correctly formatted.

Mike

"Go Terps" wrote:

Mike,

Sorry for the discrimination, but rules are rules!

I appreciate all your help, but the solution did not work. It deletes only
the top value of column A and keeps every other column the exact same. I
would like the entire row deleted if they were born more than 40 years ago I
have Excel 2003 and the spreadsheet is set up as follows with headers:

Column A - Name
Column B - Address
Column C - Line 2 address
Column D - College
Column E - City
Column F - State code
Column G - Zip Code
Column H - Birthday in xx/xx/xxxx format
Column I - Birthday #2 (If two people)

"Mike H" wrote:

Hi,

It's with some personal pain that I'm to be deleted because I'm over 40 (in
fact twice that) thatI offer you this solution to consider. Right click the
appropriate sheet tab, view code and paste this in on the right. Every time
you select the sheet those poor souls older than 40 are deleted.

Private Sub Worksheet_Activate()
Dim MyRange, MyRange1 As Range
lastrow = Cells(Rows.Count, "H").End(xlUp).Row
Set MyRange = Range("H1:H" & lastrow)
For Each c In MyRange
If IsDate(c.Value) And Int((Date - c.Value) / 365.25) 40 Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Select
End If
Selection.Delete
Range("A1").select
End Sub

Mike

"Go Terps" wrote:

I have a spreadsheet with birthdates listed in column H. Is it possible to
have certain rows automatically deleted when they hit 40 years old (or older)?

I tried a few different times after I formatted the column to both normal
date and customer date with mm/dd/yyyy and neither worked.

Sorry to bother you, but do you have any other suggestions?

"Mike H" wrote:

I find that difficult to explain because i've tested it again and it works
perfectly with properly formatted dates in Column H which I suspect may be
your problem. I suggest you check the dates are correctly formatted.

Mike

"Go Terps" wrote:

Mike,

Sorry for the discrimination, but rules are rules!

I appreciate all your help, but the solution did not work. It deletes only
the top value of column A and keeps every other column the exact same. I
would like the entire row deleted if they were born more than 40 years ago I
have Excel 2003 and the spreadsheet is set up as follows with headers:

Column A - Name
Column B - Address
Column C - Line 2 address
Column D - College
Column E - City
Column F - State code
Column G - Zip Code
Column H - Birthday in xx/xx/xxxx format
Column I - Birthday #2 (If two people)

"Mike H" wrote:

Hi,

It's with some personal pain that I'm to be deleted because I'm over 40 (in
fact twice that) thatI offer you this solution to consider. Right click the
appropriate sheet tab, view code and paste this in on the right. Every time
you select the sheet those poor souls older than 40 are deleted.

Private Sub Worksheet_Activate()
Dim MyRange, MyRange1 As Range
lastrow = Cells(Rows.Count, "H").End(xlUp).Row
Set MyRange = Range("H1:H" & lastrow)
For Each c In MyRange
If IsDate(c.Value) And Int((Date - c.Value) / 365.25) 40 Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Select
End If
Selection.Delete
Range("A1").select
End Sub

Mike

"Go Terps" wrote:

I have a spreadsheet with birthdates listed in column H. Is it possible to
have certain rows automatically deleted when they hit 40 years old (or older)?

Hi,

Upload a sample of your workbook here and post the link. I'll look in the
morning.

http://www.savefile.com/

Mike

"Go Terps" wrote:

I tried a few different times after I formatted the column to both normal
date and customer date with mm/dd/yyyy and neither worked.

Sorry to bother you, but do you have any other suggestions?

"Mike H" wrote:

I find that difficult to explain because i've tested it again and it works
perfectly with properly formatted dates in Column H which I suspect may be
your problem. I suggest you check the dates are correctly formatted.

Mike

"Go Terps" wrote:

Mike,

Sorry for the discrimination, but rules are rules!

I appreciate all your help, but the solution did not work. It deletes only
the top value of column A and keeps every other column the exact same. I
would like the entire row deleted if they were born more than 40 years ago I
have Excel 2003 and the spreadsheet is set up as follows with headers:

Column A - Name
Column B - Address
Column C - Line 2 address
Column D - College
Column E - City
Column F - State code
Column G - Zip Code
Column H - Birthday in xx/xx/xxxx format
Column I - Birthday #2 (If two people)

"Mike H" wrote:

Hi,

It's with some personal pain that I'm to be deleted because I'm over 40 (in
fact twice that) thatI offer you this solution to consider. Right click the
appropriate sheet tab, view code and paste this in on the right. Every time
you select the sheet those poor souls older than 40 are deleted.

Private Sub Worksheet_Activate()
Dim MyRange, MyRange1 As Range
lastrow = Cells(Rows.Count, "H").End(xlUp).Row
Set MyRange = Range("H1:H" & lastrow)
For Each c In MyRange
If IsDate(c.Value) And Int((Date - c.Value) / 365.25) 40 Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Select
End If
Selection.Delete
Range("A1").select
End Sub

Mike

"Go Terps" wrote:

I have a spreadsheet with birthdates listed in column H. Is it possible to
have certain rows automatically deleted when they hit 40 years old (or older)?

Simply re-formatting bogus dates will not change them from bogus to good.

If they were entered incorrectly and Excel sees them as text, you will have to
re-enter them.

If your Windows short date setting is dd/mm/yyyy and you enter dates as
mm/dd/yyyy Excel will not see those as real dates. no matter how you try to
re-format.

Running them thought DataText to Columns most times helps.


Gord Dibben MS Excel MVP

On Tue, 22 Apr 2008 14:28:00 -0700, Go Terps
wrote:

I tried a few different times after I formatted the column to both normal
date and customer date with mm/dd/yyyy and neither worked.

Sorry to bother you, but do you have any other suggestions?

"Mike H" wrote:

I find that difficult to explain because i've tested it again and it works
perfectly with properly formatted dates in Column H which I suspect may be
your problem. I suggest you check the dates are correctly formatted.

Mike

"Go Terps" wrote:

Mike,

Sorry for the discrimination, but rules are rules!

I appreciate all your help, but the solution did not work. It deletes only
the top value of column A and keeps every other column the exact same. I
would like the entire row deleted if they were born more than 40 years ago I
have Excel 2003 and the spreadsheet is set up as follows with headers:

Column A - Name
Column B - Address
Column C - Line 2 address
Column D - College
Column E - City
Column F - State code
Column G - Zip Code
Column H - Birthday in xx/xx/xxxx format
Column I - Birthday #2 (If two people)

"Mike H" wrote:

Hi,

It's with some personal pain that I'm to be deleted because I'm over 40 (in
fact twice that) thatI offer you this solution to consider. Right click the
appropriate sheet tab, view code and paste this in on the right. Every time
you select the sheet those poor souls older than 40 are deleted.

Private Sub Worksheet_Activate()
Dim MyRange, MyRange1 As Range
lastrow = Cells(Rows.Count, "H").End(xlUp).Row
Set MyRange = Range("H1:H" & lastrow)
For Each c In MyRange
If IsDate(c.Value) And Int((Date - c.Value) / 365.25) 40 Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Select
End If
Selection.Delete
Range("A1").select
End Sub

Mike

"Go Terps" wrote:

I have a spreadsheet with birthdates listed in column H. Is it possible to
have certain rows automatically deleted when they hit 40 years old (or older)?

One way
Sub overage()
For Each c In Range("a2:a82")
If (Year(Date) - Year(c)) 40 Then c.entirerow.delete
Next
End Sub
or a helper column and autofilter
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Go Terps" <Go wrote in message
...
I have a spreadsheet with birthdates listed in column H. Is it possible to
have certain rows automatically deleted when they hit 40 years old (or
older)?

Mr. Guilett,

I have excel 2003 and am unable to make this solution work. I right-click
on the tab and view source, past your suggest in, save the spreadsheet, and
re-open it and nothing happens.

Thank you,

Justin

"Don Guillett" wrote:

One way
Sub overage()
For Each c In Range("a2:a82")
If (Year(Date) - Year(c)) 40 Then c.entirerow.delete
Next
End Sub
or a helper column and autofilter
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Go Terps" <Go wrote in message
...
I have a spreadsheet with birthdates listed in column H. Is it possible to
have certain rows automatically deleted when they hit 40 years old (or
older)?

My macro was an example. Change the a2:a82 to suit your range of h2:h???
The macro should be placed in a regular module and fired manually.
If all else fails, send your workbook to the address below along with this
and a complete explanation.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Go Terps" wrote in message
...
Mr. Guilett,

I have excel 2003 and am unable to make this solution work. I right-click
on the tab and view source, past your suggest in, save the spreadsheet,
and
re-open it and nothing happens.

Thank you,

Justin

"Don Guillett" wrote:

One way
Sub overage()
For Each c In Range("a2:a82")
If (Year(Date) - Year(c)) 40 Then c.entirerow.delete
Next
End Sub
or a helper column and autofilter
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Go Terps" <Go wrote in message
...
I have a spreadsheet with birthdates listed in column H. Is it possible
to
have certain rows automatically deleted when they hit 40 years old (or
older)?

I sent OP this

Sub deleterowsifover40()
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If (DateDiff("yyyy", Cells(i, "h"), Date) - _
IIf(Format(Cells(i, "h"), "mmdd") Format(Date, "mmdd"), 1, 0)) = 40 _
And Cells(i, "H") 0 Then
'Cells(i, "j") = "x"
Rows(i).Delete
End If
Next
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Go Terps" wrote in message
...
Mr. Guilett,

I have excel 2003 and am unable to make this solution work. I right-click
on the tab and view source, past your suggest in, save the spreadsheet,
and
re-open it and nothing happens.

Thank you,

Justin

"Don Guillett" wrote:

One way
Sub overage()
For Each c In Range("a2:a82")
If (Year(Date) - Year(c)) 40 Then c.entirerow.delete
Next
End Sub
or a helper column and autofilter
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Go Terps" <Go wrote in message
...
I have a spreadsheet with birthdates listed in column H. Is it possible
to
have certain rows automatically deleted when they hit 40 years old (or
older)?