Returning the 10 lowest scores that aren't Zero in a Macro

CycloneSteel

New Member
I have a golf league SS that I have been using for years for a 9 hole league but I added another 18 hole league and it's giving me fits! Some guys play the front and some play the back each week, so when I sort my normal way (rounds played over .49) when I do a top 10 for each hole, or for each 9, it's returning a bunch of zeros as the top 10 best score since half the guys didn't play the front. It's returning the overall top 10 each week just fine yet.

Here's a snippet starting from the top:
Sub Top10z()
Dim LastRow As Long, Top60Row As Long, s As String, Rng As Range, CopyRng As Range, ws As Worksheet
Set ws = Worksheets("2018 Stats")
s = Application.Calculation
With Application
.EnableEvents = False
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ws
LastRow = .Cells(Rows.Count, "AT").End(xlUp).Row
Set Rng = .Range("A2:FC" & LastRow)
With Rng
End With
Top90Row = Application.WorksheetFunction.Match(0.49, .Range("AT3:AT" & LastRow), -1) + 2
Set Rng = .Range("A2:FC" & Top90Row)
With Rng
.Sort Key1:=.Range("S3"), Order1:=xlAscending, Key2:=.Range("I3"), Order2:=xlDescending, Header:=xlYes
Set CopyRng = Union(ws.[a3:a12], ws.[i3:i12], ws.[s3:s12])
CopyRng.Copy ws.[fj2]
.Sort Key1:=.Range("T3"), Order1:=xlAscending, Key2:=.Range("I3"), Order2:=xlDescending, Header:=xlYes
Set CopyRng = Union(ws.[a3:a12], ws.[i3:i12], ws.[t3:t12])
CopyRng.Copy ws.[fp2]
.Sort Key1:=.Range("U3"), Order1:=xlAscending, Key2:=.Range("I3"), Order2:=xlDescending, Header:=xlYes
Set CopyRng = Union(ws.[a3:a12], ws.[i3:i12], ws.[u3:u12])
CopyRng.Copy ws.[fe2]

Somehow I want all the S3/T3/U3 -and many more cells -to return the top 10 non zero numbers (well, the lowest scores) -where S is the Front 9, T is the back 9 and U is the Total (they only play the front or the back each week. I am hoping to avoid having to keep a separate tab for each 9 since I want to do stats like top 10 total average and run this all on one tab, if possible! Thanks!

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

offthelip

Well-known Member
I think all you need to do is loop down the range after sorting it to find the first non zero row and then pick that up. This code should show you haw to do that ( untested)
change:
Code:
``````With Rng
.Sort Key1:=.Range("S3"), Order1:=xlAscending, Key2:=.Range("I3"), Order2:=xlDescending, Header:=xlYes
Set CopyRng = Union(ws.[a3:a12], ws.[i3:i12], ws.[s3:s12])
CopyRng.Copy ws.[fj2]``````
to
Code:
``````With Rng
.Sort Key1:=.Range("S3"), Order1:=xlAscending, Key2:=.Range("I3"), Order2:=xlDescending, Header:=xlYes
For i = 1 To lastrow
If Cells(i, 19) > 0 Then '' column 19 is column S
Exit For          ' we have found the first non blank
End If
next i
Set CopyRng = Union(ws.Range(Cells(i, 1), Cells(i + 10, 1)), ws.Range(Cells(i, 9), Cells(i + 10, 9)), ws.Range(Cells(i, 19), Cells(i + 10, 19)))``````

Note I changed the range addressing to cells becuase I am more familar with that, and it is much easier to deal with numbers using it

Last edited:

CycloneSteel

New Member
Hmm, not quite working, still returning 0.00 for the sort of the S column?

offthelip

Well-known Member
what value of i does the loop exit at?
put
Code:
``msgbox (i)``
just before the exit for to find out the easy way

another thing I notice you are sorting from row 3 so you should start the loop at 3 as well e.g
Code:
``for i = 3 to lastrow``

Last edited:

CycloneSteel

New Member
Thanks you, I'll give it a whirl!

Replies
3
Views
194
Replies
1
Views
155
Replies
8
Views
393
Replies
0
Views
217
Replies
1
Views
428

1,147,732
Messages
5,742,855
Members
423,759
Latest member
meb229

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back