Selecting rows

[Withnails]

Hi
I am looking to view the data in column k2 to the end of the available data,
and copy the rows that column that house the 3 largest values and 3 smallest
values, before copying the six selected rows into the next worksheet
(worksheet 3).
Any idea of how this can be done?

 

[Jacob Skaria]

Try the below. Adjust the sheet name to suit...

Sub ReFormat2()
Dim c As Range, rng As Range, lngRow As Long
Set rng = Range("K2:K" & Cells(Cells.Rows.Count, "K").End(xlUp).Row)
For Each c In rng
If WorksheetFunction.Large(rng, 1) = c.Value Or _
WorksheetFunction.Large(rng, 2) = c.Value Or _
WorksheetFunction.Large(rng, 3) = c.Value Or _
WorksheetFunction.Small(rng, 1) = c.Value Or _
WorksheetFunction.Small(rng, 2) = c.Value Or _
WorksheetFunction.Small(rng, 3) = c.Value Then _
lngRow = lngRow + 1: Rows(c.Row).Copy Sheets("Sheet3").Rows(lngRow)
Next
End Sub

If this post helps click Yes

 

[Simon Lloyd]

Why not simply use a formula and then copy values
Assuming your values are in column A you can use this in column
=LARGE(INDEX(A$1:A$100,LARGE(ROW(A$1:A$100)*ISNUMBER(A$1:A$100),100)):A$100,ROWS(B$1:B1))
after pasting it to the formula bar it must be comitted usin
Ctrl+Shift+Enter as its an array formula, if you have done it correctl
it will show { at either end of the formula, after you have done thi
copy down 3 cells, in the next cell (B4) do the same with this formul
=SMALL(INDEX(A$1:A$100,SMALL(ROW(A$1:A$100)*ISNUMBER(A$1:A$100),1)):A$100,ROWS(B$4:B4)
you should now have six figures 3 top and 3 bottom, simply copy an
paste the values to another sheet

H
I am looking to view the data in column k2 to the end of the availabl
data
and copy the rows that column that house the 3 largest values and
smalles
values, before copying the six selected rows into the next workshee
(worksheet 3)
Any idea of how this can be done

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[Withnails]

this works well, but grabs data from column K on sheet1, how can i adjust it
to grab info from sheet2?

 

[Jacob Skaria]

Try the below code...

Sub ReFormat2()
Dim c As Range, rng As Range, lngRow As Long, Dim ws as Worksheet
Set ws = Worksheets("Sheet2")
Set rng = ws.Range("K2:K" & ws.Cells(Cells.Rows.Count, "K").End(xlUp).Row)
For Each c In rng
If WorksheetFunction.Large(rng, 1) = c.Value Or _
WorksheetFunction.Large(rng, 2) = c.Value Or _
WorksheetFunction.Large(rng, 3) = c.Value Or _
WorksheetFunction.Small(rng, 1) = c.Value Or _
WorksheetFunction.Small(rng, 2) = c.Value Or _
WorksheetFunction.Small(rng, 3) = c.Value Then _
lngRow = lngRow + 1: ws.Rows(c.Row).Copy Sheets("Sheet3").Rows(lngRow)
Next
End Sub

If this post helps click Yes