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)?

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
(E-Mail Removed)
"Go Terps" <Go (E-Mail Removed)> wrote in message
news:CB55E6E3-C1B6-4CB2-A73D-(E-Mail Removed)...
>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)?

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
> (E-Mail Removed)
> "Go Terps" <Go (E-Mail Removed)> wrote in message
> news:CB55E6E3-C1B6-4CB2-A73D-(E-Mail Removed)...
> >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)?