Tom and Richard... On the Right Track (Excel Query)

Tom's a bit more of a pro at this stuff than me I think, but a possible suggestion might be to name the range that has all your data in it, and use Tom's formula to rank them, but instead of the cell references in the second argument, use the name. Hopefully this will mean that when your list is updated, you can just copy Tom's formula from top to bottom each time, and not have to re-enter it. You could then have the whole lot with a different name, sya "current year", and on a separate sheet use vlookups to return the top 10 names and times, or however many you want. The only manual stuff you would have to do is copy Tom's formula, and sort the list so the lookup works. You could probably right a Macro to do this.
Good Luck!!
Richard

Is this what you are looking for?

Brett,

Here's a macro that I think does what you want.
This selects the "top" 10 times (I put "top" in quotes because really I think you want the lowest times which are usually winners in races)...note that the code calls for "Bottom" in this case.

This macro assumes that:

(1) You have data from column A to column E; adjust as needed.

(2) Your worksheet is Sheet5, adjust as needed.

(3) Your times are in column B, adjust as needed.

(4) Your data begins in A2, with row 1 containing headers.

Finally, this will work no matter how many rows your data fills up as you add to it, hence the unoriginal name of the sub because it serves a dynamic range.

Hope this helps, here it is.

Sub FilterDynamic()
' FilterDynamic Macro
' Macro recorded 10/24/2001 by Thomas Urtis

'Turn off screen updating
Application.ScreenUpdating = False
'Identify your dynamic range
Dim rList As Range
Set rList = Sheet5.Range("A2", Sheet5.Range("E65536").End(xlUp))
'Turn off the AutoFilter if it is on
Sheet5.AutoFilterMode = False
'Now turn the AutoFilter on
With rList
.Offset(0, 2).Cells(1, 1).AutoFilter
End With
'Execute the filter for top 10 times
Selection.AutoFilter Field:=2, Criteria1:="10", Operator:=xlBottom10Items
'Turn screen updating back on
Application.ScreenUpdating = True
End Sub

Thank you Richard for the nice compliment in your previous post.

Tom Urtis

Re: Is this what you are looking for?

Tom,

That Macro looks great except that VBA doesnt like the line: Selection.AutoFilter. Field:=2, Criteria1:="10", Operator:=xlBottom10Items

When it gets ot this point, it says "Application Defined or Object Defined Error" and highlights that line. Any ideas?

Brett

P.S. You and Richard amaze me, I never thought anyone could know this much about a program...shows what I know. Thanks for taking the time.

Re: Is this what you are looking for?

Brett,

I tested this and it worked for me before I sent it to you, so there may be a few reasons why:

(1) If you pasted this into a module straight out of the message board, sometimes line breaks and spaces occur where none existed before.

(2) Maybe your sheet references are incorrect. In my example, Sheet5 may not necessarily mean what you see as Sheet 5 on your worksheet tab. Go into the VBE and see which sheet number Excel thinks your actual worksheet in question is, and adjust the code accordingly.

(3) Maybe your actual range is not consistent with how I wrote the macro example? Maybe your times are in a different column than B or start in a different row and that needs to be adjusted.

If you still have problems, email it to me at tomurtis@home.com and I can look it over later tonight.

Tom