I have two columns, G and I, that I used together. G has a number
in it and I has a date/time stamp. Here is an example:

18.32 10/2/08 14:49
19.99 9/26/08 21:36


Not all the cells are filled in. Many are blank. There
are about 200 or fewer rows in the sheet.

I want to have a macro delete entries from both columns
based on ranked aging of the date/time stamp. Leave the
X newest entries (such as 3 or 5 or 10) and delete others.

I started to code this but ran into trouble. Here
is what I have. Can someone help me get this to work?
It would be much appreciated.

'--------------
Dim cell As Range
Dim testI As Range

' Some unrelated stuff deleted ...

For Each cell In Range("G2:G500")
If cell <> "" Then
Set testI = cell.Offset(0, 2)
Debug.Print testI
If Not Application.WorksheetFunction.Rank(testI, _
Range("G2:G500")) < 6 Then
Range(cell, testI).Clear
End If
End If
Next 'cell
'--------------

Thanks for any help, guys.

=dman=

I'd guess you'd want to calculate the rank against the same cell, but you
aren't doing that. You change it every iteration through the For/Next loop.
Is that what you want to do?
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Dallman Ross" wrote:

> I have two columns, G and I, that I used together. G has a number
> in it and I has a date/time stamp. Here is an example:
>
> 18.32 10/2/08 14:49
> 19.99 9/26/08 21:36
>
>
> Not all the cells are filled in. Many are blank. There
> are about 200 or fewer rows in the sheet.
>
> I want to have a macro delete entries from both columns
> based on ranked aging of the date/time stamp. Leave the
> X newest entries (such as 3 or 5 or 10) and delete others.
>
> I started to code this but ran into trouble. Here
> is what I have. Can someone help me get this to work?
> It would be much appreciated.
>
> '--------------
> Dim cell As Range
> Dim testI As Range
>
> ' Some unrelated stuff deleted ...
>
> For Each cell In Range("G2:G500")
> If cell <> "" Then
> Set testI = cell.Offset(0, 2)
> Debug.Print testI
> If Not Application.WorksheetFunction.Rank(testI, _
> Range("G2:G500")) < 6 Then
> Range(cell, testI).Clear
> End If
> End If
> Next 'cell
> '--------------
>
> Thanks for any help, guys.
>
> =dman=
>

Clarifying, when I try to run my code below I get "Run-time
error '1004': Unable to get the Rank property of the WorksheetFunction
class." What am I doing wrong, please? Can you help?

----------------
In <gc3gvs$i9o$(E-Mail Removed)>, Dallman Ross <dman@localhost.>
spake thusly:

> I have two columns, G and I, that I used together. G has a number
> in it and I has a date/time stamp. Here is an example:
>
> 18.32 10/2/08 14:49
> 19.99 9/26/08 21:36
>
>
> Not all the cells are filled in. Many are blank. There
> are about 200 or fewer rows in the sheet.
>
> I want to have a macro delete entries from both columns
> based on ranked aging of the date/time stamp. Leave the
> X newest entries (such as 3 or 5 or 10) and delete others.
>
> I started to code this but ran into trouble. Here
> is what I have. Can someone help me get this to work?
> It would be much appreciated.
>
> '--------------
> Dim cell As Range
> Dim testI As Range
>
> ' Some unrelated stuff deleted ...
>
> For Each cell In Range("G2:G500")
> If cell <> "" Then
> Set testI = cell.Offset(0, 2)
> Debug.Print testI
> If Not Application.WorksheetFunction.Rank(testI, _
> Range("G2:G500")) < 6 Then
> Range(cell, testI).Clear
> End If
> End If
> Next 'cell
> '--------------
>
> Thanks for any help, guys.
>
> =dman=