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

CycloneSteel

New Member
Joined
May 10, 2004
Messages
11
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
.Sort Key1:=Range("AT3"), Order1:=xlDescending, Header:=xlYes
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
Joined
Dec 23, 2017
Messages
2,144
Office Version
  1. 2010
Platform
  1. Windows
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:

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,144
Office Version
  1. 2010
Platform
  1. Windows
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:

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top