Delete cells below a certain rank
 

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=

Delete cells below a certain rank
 

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=

Delete cells below a certain rank
 

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 , 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=