Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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= |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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= |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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= |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rank the numbers / range of data using 'RANK' and 'ABS' | Excel Worksheet Functions | |||
Rank a column but not include some cells | Excel Discussion (Misc queries) | |||
RANK, duplicate ranking but no gaps in rank | Excel Worksheet Functions | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) | |||
copmapre and rank cells | Excel Programming |