Copying a dynamic table range

[BlahQz]

Hi I'm fairly new to Excel so please excuse if my terminology may be wrong. Apologies in advance

What I'm looking for is a macro code that will copy a range of data, basically a top 10, that may vary in size.

In K3:K25 are a list of random names.
In L3:L25 are a list of random numbers that correspond to those names.
In M3:M25 are the rankings based on the numbers in the L range (highest number is rank 1 etc). The data is already sorted by rank.

What I'm looking for is a code that will just look at the full range from K3:M25 then only select and copy just the top 10 ranks as sometimes there may be double or triples of the same rank which would affect the number of rows to be copied. I don't need to paste it anywhere, just selected and copied.

I hope this makes sense, but if not I can supply a visual for it. I appreciate any help I can get on this. Thank you.

 

[mumps]

Assuming that you have headers in K2:M2, try:

Sub CopyRange()
    Dim lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("K2:M" & lRow).AutoFilter Field:=3, Criteria1:="<=10"
    Range("K3:M" & lRow).SpecialCells(xlVisible).Copy
End Sub

 

[AlphaFrog]

this will find the last ranking of 10 in column M and copy the rows above.

Sub Top_10()
    Dim Top10 As Range
    Set Top10 = Range("M:M").Find(10, [M26], xlValues, xlWhole, xlByRows, xlPrevious, False)
    If Not Top10 Is Nothing Then
        Range("K3:" & Top10.Address).Copy
    Else
        MsgBox "Could not locate the 10th ranked entry.", vbExclamation, "Top 10 Macro Error"
    End If
End Sub

 

[BlahQz]

Assuming that you have headers in K2:M2, try:

Sub CopyRange()
    Dim lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("K2:M" & lRow).AutoFilter Field:=3, Criteria1:="<=10"
    Range("K3:M" & lRow).SpecialCells(xlVisible).Copy
End Sub

Wow this is excellent thank you. It's definitely closer than anything I've been able to do and it does work. The only issue with it is that it deletes the data in the rest of the K3:K25 range which I wasn't looking to have happen, perhaps I should have specified that, but it definitely copies to top 10 ranks as it should.

 

[BlahQz]

this will find the last ranking of 10 in column M and copy the rows above.

Sub Top_10()
    Dim Top10 As Range
    Set Top10 = Range("M:M").Find(10, [M26], xlValues, xlWhole, xlByRows, xlPrevious, False)
    If Not Top10 Is Nothing Then
        Range("K3:" & Top10.Address).Copy
    Else
        MsgBox "Could not locate the 10th ranked entry.", vbExclamation, "Top 10 Macro Error"
    End If
End Sub

This for sure works Alphafrog. This is what I was looking for I really appreciate everyone's help here!