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?

Sarah,

Try code like the following. Change the reference to A1:A100 to the
appropriate range.

Sub AAA()
Dim Keep1 As Date
Dim Keep2 As Date
Dim R As Range
Dim RR As Range

Keep1 =
CDate(Application.WorksheetFunction.Large(Range("A1:A100"), 10))
Keep2 = Application.WorksheetFunction.Max(Range("A1:A100"))

Set R = Cells(Rows.Count, "A").End(xlUp)
Do Until R.Row = 1
If Keep1 <= R.Value And R.Value <= Keep2 Then
' R is within 10 entries. do nothing.
Else
If RR Is Nothing Then
Set RR = R
Else
Set RR = Application.Union(RR, R)
End If

End If
Set R = R(0, 1)
Loop
If Not RR Is Nothing Then
RR.EntireRow.Delete
End If
End Sub

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
The San Diego Project Group, LLC
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)


On Sat, 11 Oct 2008 18:28:49 +0200, "Sarah H." <(E-Mail Removed)>
wrote:

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

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." <(E-Mail Removed)> wrote in message
news:gcqk83$8ig$(E-Mail Removed)...
> 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?
>

Sorry about the subroutine name (I reused a macro from another question I
answered early)... the code should work fine as posted... just change the
subroutine name to anything you think is more proper if you want.

--
Rick (MVP - Excel)


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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." <(E-Mail Removed)> wrote in message
> news:gcqk83$8ig$(E-Mail Removed)...
>> 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?
>>
>

Most interesting approach, Chip. Thank you.
I studied it until I could see what you did. Very useful for me!
For example, I found it interesting how you saved up the union
of rows to delete and then did the actual deletion at the end.

One thing is, I don't want to delete the whole row for dates older
than the most recent 10 -- but only the older dates in the date column.
I figured out how to change your code to do that, though.

I also found Rick's code to be extremely useful. Both of these
are excellent, and I am grateful! I can now do what I need to do.

I will respond to Rick separately.

Sarah
------------------
"Chip Pearson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Sarah,
>
> Try code like the following. Change the reference to A1:A100 to the
> appropriate range.
>
> Sub AAA()
> Dim Keep1 As Date
> Dim Keep2 As Date
> Dim R As Range
> Dim RR As Range
>
> Keep1 =
> CDate(Application.WorksheetFunction.Large(Range("A1:A100"), 10))
> Keep2 = Application.WorksheetFunction.Max(Range("A1:A100"))
>
> Set R = Cells(Rows.Count, "A").End(xlUp)
> Do Until R.Row = 1
> If Keep1 <= R.Value And R.Value <= Keep2 Then
> ' R is within 10 entries. do nothing.
> Else
> If RR Is Nothing Then
> Set RR = R
> Else
> Set RR = Application.Union(RR, R)
> End If
>
> End If
> Set R = R(0, 1)
> Loop
> If Not RR Is Nothing Then
> RR.EntireRow.Delete
> End If
> End Sub
>
> Cordially,
> Chip Pearson
> Microsoft MVP
> Excel Product Group
> Pearson Software Consulting, LLC
> www.cpearson.com
> The San Diego Project Group, LLC
> (email is on the web site)
> USA Central Daylight Time (-5:00 GMT)
>
>
> On Sat, 11 Oct 2008 18:28:49 +0200, "Sarah H." <(E-Mail Removed)>
> wrote:
>
>>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?
>>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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." <(E-Mail Removed)> wrote in message
> news:gcqk83$8ig$(E-Mail Removed)...
>> 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?
>>
>

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." <(E-Mail Removed)> wrote in message
news:gcss28$4lg$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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." <(E-Mail Removed)> wrote in message
>> news:gcqk83$8ig$(E-Mail Removed)...
>>> 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?
>>>
>>
>
>

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." <(E-Mail Removed)> wrote in message
news:gcsulv$e4g$(E-Mail Removed)...
> 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." <(E-Mail Removed)> wrote in message
> news:gcss28$4lg$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>>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." <(E-Mail Removed)> wrote in message
>>> news:gcqk83$8ig$(E-Mail Removed)...
>>>> 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?
>>>>
>>>
>>
>>
>
>

Rick,

Thanks a lot -- I appreciate the further explanation. Interesting as to the
handling of multiple dates. Yes, in my case, it's not a problem, but I
will keep the consideration in mind for this situation should it develop
differently and also for further VBA uses.

Sarah


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)