macro to extract 10 highest values from 1 column onto new sheet

[bluefish44]

Thanks in advance.....

I have a tab called 'Medium rollup'. On this tab, starting in cell F4 going down I have a set of values. I'd like to create a macro where it will find the10 highest values from this column and then paste the entire rows that contain these values on a sheet called 'Top 10' starting in row 2. My VBA isn't up to snuff to get this done. Ideas?

 

[VBA Geek]

Sub getHigh10()
Dim lRow As Long
lRow = Sheets("Medium Rollup").Range("F" & Rows.Count).End(xlUp).Row
Sheets("Top 10").Range("A2:A11").Value = Evaluate("transpose(LARGE('Medium Rollup'!F4:F" & lRow & ",{1,2,3,4,5,6,7,8,9,10}))")
End Sub

 

[bluefish44]

Thanks VBA Geek - This correctly pastes the 10 highest values in column A, but instead are you able to alter the code to paste the entire rows that contain these values?

 

[VBA Geek]

Sub hi()
Dim lRow As Long
lRow = Sheets("Medium Rollup").Range("F" & Rows.Count).End(xlUp).Row
Range(Join(Filter(Evaluate(Replace("=IF(TRANSPOSE($F$4:$F$#)>=LARGE($F$4:$F$2#,10),TRANSPOSE(ADDRESS(ROW($F$4:$F$#),6,,,""Medium Rollup"")),""@@"")", "#", lRow)), "@@", 0), ",")).EntireRow.Copy _
        Sheets("Top 10").Range("A2")


End Sub

 

[bluefish44]

gorgeous! - 1 last request. The first code you gave ordered the numbers in descending order on the 'top 10' sheet. Your new code pastes all of the rows correctly, except it now orders them alphabetically by column A. Instead, I would like them to continue to be ordered by column F in descending order (Largest on top).

 

[VBA Geek]

How about sorting it by column F after it has been imported onto sheet Top 10?

 

[ben2129]

This code is exactly what I need for my app. The first section of code runs fine on my data, the second, which copies the entire row returns a "Run-Time Error 13, type mismatch". I've also tried the code on a data set of only numbers and get the same error. I've researched the error but have not discovered a resolution. Running Windows 8 and Excel 2013. I'm well versed in excel but not VB. Below is a small segment of my data, any help will be appreciated:

[TABLE="width: 382"]
<colgroup><col><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD][TABLE="width: 382"]
<colgroup><col><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD]Left Jab[/TD]
[TD]326[/TD]
[TD]20.5[/TD]
[TD]2015-08-14[/TD]
[TD]11:23:35:177[/TD]
[/TR]
[TR]
[TD]Right Hook[/TD]
[TD]441[/TD]
[TD]11.8[/TD]
[TD]2015-08-14[/TD]
[TD]11:23:35:425[/TD]
[/TR]
[TR]
[TD]Left Hook[/TD]
[TD]426[/TD]
[TD]16.8[/TD]
[TD]2015-08-14[/TD]
[TD]11:23:38:063[/TD]
[/TR]
[TR]
[TD]Left Hook[/TD]
[TD]440[/TD]
[TD]16.5[/TD]
[TD]2015-08-14[/TD]
[TD]11:23:38:357[/TD]
[/TR]
[TR]
[TD]Left Hook[/TD]
[TD]524[/TD]
[TD]16.3[/TD]
[TD]2015-08-14[/TD]
[TD]11:23:38:729[/TD]
[/TR]
[TR]
[TD]Left Jab[/TD]
[TD]352[/TD]
[TD]18.8[/TD]
[TD]2015-08-14[/TD]
[TD]11:23:41:751[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]