Selecting rows

  • Thread starter Thread starter Withnails
  • Start date Start date
W

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?
 
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
 
this works well, but grabs data from column K on sheet1, how can i adjust it
to grab info from sheet2?
 
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
 
Back
Top