Best way to remove old dated entries

[niravrph]

Hi all,

I have a spreadsheet that has about 5000 rows. Each of these rows has a date e.g. 03/14/2010, 11/27/2011 etc. I want to remove all dates that are 2010 and show only those with 2011. What's the best way to do that without Auto-filtering and manually removing each 2010 entry one by one? There has to be an easier way..... (I hope).

Thank you!
NIRAV

 

[VoG]

Assuming the dates are in column A

Sub test3()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Year(Range("A" & i).Value) < 2011 Then Rows(i).Delete
Next i
End Sub

 

[JackDanIce]

VBA:
Filter for dates < 01/01/2011
Delete the visible rows
Remove Filter

 

[CWatts]

You dont have to delete one by one with autofilter. When you auto filter have it show all the 2010 dates, then you can highlight all the rows on the left and right click and choose delete row.

Then turn off the autofilter and you're left with 2011.

Is there another reason preventing you from using autofilter?

 

[niravrph]

Hi all,

Thanks for your help. Unfortunately my company's offering of Excel 2007 does not allow VBA for some reason. It is frustrating to be sure. Thank you for your suggestions though.

CWatts - can you show me how to auto-filter the 2010 dates? When I click Auto-Filter it lists hundreds of exact dates such as 03/11/2010, 10/15/2010 etc.... how do I "by-pass" these exact dates and instruct Excel to filter on just a general 2010 number, such as you suggest above?

Thank you,
NIRAV

 

[CWatts]

When you filter, before your big list of repeating dates and times, there is going to be something that will say Date filter or Number filter.

if it says date filter, it'll be more intuitive. If it says number filter you want the filter to show everything that is less than 40544. 40544 is the serial date for 1/1/2011 so as long as everything is LESS than that date (not less than or equal to) then that will leave you with only 2011 dates.

Hope that helps!