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?