Sort excel and get top 10 and bottom 10 rows

[atulc]

Can someone tell me how do I get top 10 and bottom 10 of a sorted data using VBA? I am using the following code to sort

Range("B26:EE106").Select
Selection.Sort key1:=Range("BF27"), order1:=xlAscending, _
header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


I am not sure if there is a better way to sort but I can live with this. But now I am stuck as to how do I get the top 10 and bottom 10 values from column BF and put it on another sheet?

Thanks

A

[Ago]

sheets("sheet2").range("A1", "A11").value = sheets("sheet1").range("BF26", "BF36").value
sheets("sheet2").range("B1", "B11").value = sheets("sheet1").range("BF96", "BF106").value


but you need to change the sheetnames to what you have.
im assuming the range you used represents top and bottom of the list

[atulc]

Thanks for the suggestion.

How will this change if the size of the range is not fixed?

[Ago]

bottom = Cells(Rows.Count, "BF").End(xlUp).Row

sheets("sheet2").range("A1", "A11").value = sheets("sheet1").range("BF26", "BF36").value
sheets("sheet2").range("B1", "B11").value = sheets("sheet1").range("BF" & bottom - 10, "BF" & bottom).value




this only works if only the "bottom moves", im assuming the top is fixed.
or does the top of the list also move?