Automatic Conditional Row Deletion?

[Go Terps]

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 H]

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

 

[Don Guillett]

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

 

[Barb Reinhardt]

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]

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

 

[Go Terps]

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)

 

[Go Terps]

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

 

[Mike H]

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]

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]

Hi,

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

http://www.savefile.com/

Mike

 

[Don Guillett]

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.

 

[Gord Dibben]

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 Data>Text to Columns most times helps.


Gord Dibben MS Excel MVP

 

[Don Guillett]

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