Top 6 in column with unsorted data: in Excel

leeloo0505

Board Regular
Joined
Mar 28, 2003
Messages
130
Hello, Trying to get the top 6 (where a 1 value is considered higher than 10)of a column that is not presorted or filtered.. I have the code below but it only goes through the column once producing only the top 2 when I want the top 6. I got some code from an archived post (thanks jimboy).
Here is my code:
Private Sub cmdQ12004_Click()
Dim c As Range
Dim rank As Long
Dim iQ1row As Long
Dim getrow As Long
Dim visiblerange As Range
Set visiblerange = Range("L6:L27").SpecialCells(xlCellTypeVisible)
iQ1row = Sheets("Q1").Range("N20").End(xlUp).Row + 1
rank = 6
For Each c In Range("L6:L27").SpecialCells(xlCellTypeVisible)
getcell = c.Address
If Len(getcell) = 5 Then
getrow = Right(getcell, 2)
Else
getrow = Right(getcell, 1)
End If
If c.Value = WorksheetFunction.Small(visiblerange, rank) Then
Sheets("Q1").Range("N" & iQ1row + rank) = Cells(getrow, 2).Value
rank = rank - 1
End If

Next c
End Sub
I am getting the row address cuz the value I want to put in ranking is not what is being evaluated, rather a persons name in the B(2) column.
so once c reaches 27 (where the column stops) it ends.. not relooping to give the other 4.

Thanks for your time,
leeloo0505
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

jimboy

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
Hi - with works for me on unfiltered data.

If your data is not filtered why are you using the SpecialCells(xlcelltypeVisible) line?
 

CraigM

Active Member
Joined
Feb 27, 2003
Messages
320
How about:

Sub cmdQ12004_Click()
Dim c As Range, VisibleRange As Range
Dim Rank As Byte
Set VisibleRange = Range("L6:L27").SpecialCells(xlCellTypeVisible)

Rank = 1
Do
For Each c In VisibleRange
If c.Value = WorksheetFunction.Small(VisibleRange, Rank) Then
Sheets("Q1").Range("N20").End(xlUp).Offset(1, 0) = Cells(c.Row, 2).Value
Rank = Rank + 1
If Rank > 6 Then Exit Do
End If
Next c
Loop
End Sub
 

leeloo0505

Board Regular
Joined
Mar 28, 2003
Messages
130
Thanks Craig..

That is exactly what I ended up doing..

Jimboy:

Well to be honest I just copied some code of yours from another message concerning getting top values.. not sure exactly what it does so I thought I would use it and see what happens.

leeloo
 

jimboy

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
It will only run on visible cells, so if you have filtered cell it will ignore them.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,100
Messages
5,768,091
Members
425,452
Latest member
htay44

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