First, let me say what a joy it was to read your response... you actually
read and analyze the code posted in response to your questions in an effort
to understand how it works. All too often, the code we volunteers post on
the newsgroups simply gets copied into a poster's code without them giving
it a second thought.

Okay, as to your pending questions (I see from your other response to this
sub-thread that you got the error problem resolved)...

And what is CDate? Both you and Chip used it.
Well, now I've found what it is via a web search,
but I still don't quite see why the value in the date
column isn't sufficient on its own so that we need
to convert it to CDate. Maybe you can explain
that part?)

You are probably right... CDate may not be needed. The thing I was concerned
about (and didn't test any further) was that the underlying value for a date
is an integer value and that Format might not automatically coerce it to a
date before attempting to operate on it. In a quick test, it looks like the
Format function does indeed perform the coercion (probably because d, m and
y is being used in the "format string"), so you can probably safely remove
the CDate function call (although explicitly performing the coercion can
never be wrong).

After I digested yours and Chip's I started thinking
of a new approach. I don't know if it's better or
worse, but it makes sense to me, so I'll ask what you
guys think: what about just finding the 10th most recent
date (via the same "WorksheetFunction.Large(...)"
technique you both suggested) and then simply deleting
cells that are smaller than that value?

That may work for you depending on whether you can have duplicate dates and
what you actually meant by 1. The method I used creates a range of the
**first** 10 largest dates it finds and uses that to parse away the rest of
the dates. Where this matters is with a set of dates like this... 1/1/2008,
1/1/2008, 1/1/2008, 1/1/2008, 1/1/2008, 1/2/2008, 1/3/2008, 1/4/2008,
1/5/2008, 1/6/2008, 1/7/2008, 1/8/2008... note there are 12 dates in the set
with the first 5 of them being the same. My method will take the last 10 of
them and delete the first two dates (even though they match other dates in
the set) along with all earlier dates leaving 10 cells not deleted. Your
method, because if finds the 10th largest date and deletes all previous
dates will leave 12 cells not deleted. If you will not have duplicated
dates, then either method will work the same and your proposed method would
probably be the simpler one.

--
Rick (MVP - Excel)


"Sarah H." wrote in message
...
Whoops, Rick -- my bad. Your code does work.
The problem was that I tested it when I had fewer than 10
dates in the date column. I added an "On Error" statement
and now it works. Still wondering about my other questions,
though. Thanks again!

Sarah
-----------------

"Sarah H." wrote in message
...
Rick,

Your code helped me immensely. Thank you. (The name of the
macro did not confuse me beyond a few seconds, but thank you for
the follow-up clarification.) I think I mostly understand what
you did.

I have to admit that part of it didn't seem to work. I get a
run-time error 1004, Unable to get the Large property, at this
part (from the Else statement):

Set LargeDateCells = Union(LargeDateCells, _
.Columns(DateColumn). _
Find(CDate(WorksheetFunction. _
Large(.Columns(DateColumn), X))))

Although I don't see exactly why the error occurs, I will add
that I have now used a combination of what you and Chip wrote to get
an answer that works fine for me. Still, I am curious as to what
went wrong here -- if you don't mind looking further. (And what
is CDate? Both you and Chip used it. Well, now I've found what it
is via a web search, but I still don't quite see why the value in the
date
column isn't sufficient on its own so that we need to convert it
to CDate. Maybe you can explain that part?)

After I digested yours and Chip's I started thinking of a new approach.
I don't know if it's better or worse, but it makes sense to me, so I'll
ask
what you guys think: what about just finding the 10th most recent date
(via the same "WorksheetFunction.Large(...)" technique you both
suggested) and then simply deleting cells that are smaller than that
value? Well, that's what I've now coded.

I still have to see about error-checking in case there aren't 10 dates,
though. But wait -- if there aren't, I don't need to delete anything
anyway, so I can just exit on such an error, I guess.

Thanks so much, again.

Sarah


"Rick Rothstein" wrote in message
...
I think this code will do what you want (change the worksheet reference
in the With statement and the DateStartRow and DateColumn Const
statements to suit your needs)...

Sub CheckForSixItems()
Dim X As Long
Dim R As Range
Dim LastRow As Long
Dim LargeDateCells As Range
Const DateStartRow As Long = 2
Const DateColumn As String = "B"
With Worksheets("Sheet2")
LastRow = .Cells(.Rows.Count, DateColumn).End(xlUp).Row
For X = 1 To 10
If LargeDateCells Is Nothing Then
Set LargeDateCells = .Columns(DateColumn). _
Find(CDate(WorksheetFunction. _
Large(.Columns(DateColumn), X)))
Else
Set LargeDateCells = Union(LargeDateCells, _
.Columns(DateColumn). _
Find(CDate(WorksheetFunction. _
Large(.Columns(DateColumn), X))))
End If
Next
For Each R In Range(.Cells(DateStartRow, DateColumn), _
.Cells(LastRow, DateColumn))
If Intersect(R, LargeDateCells) Is Nothing Then R.Clear
Next
End With
End Sub

--
Rick (MVP - Excel)


"Sarah H." wrote in message
...
Hi, all,

I'm stuck and hope you can offer some help.

I know a bit about VBA programming but am having trouble writing
code to do the following: I wish to delete all date values in a column
of dates except for the most recent 10 entries. Can someone show me
how?