Top 6 in column with unsorted data: in Excel

[leeloo0505]

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

 

[jimboy]

Hi - with works for me on unfiltered data.

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

 

[CraigM]

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]

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]

It will only run on visible cells, so if you have filtered cell it will ignore them.