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

 

[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