Drop 3 Lowest Entries

[Dennis]

I have a list in B2 thru B11 which includes in this order.... 15 10 48 16 27
8 2 21 10 60

What I woujld like to do is highlight the range and have the 3 lowest numbers
deleted, but I dont want them sorted first, the remaining 7 must stay in the
original cells. Here's where it gets tricky, because 10 is one of the lowest 3
and there are 2 10's, I want to keep one of the 10's (it doesn't matter which
one). So after all is done I would be left with 15 48 16 27 21 10 60

TIA, Dennis
================

 

[Bob Phillips]

Hi Dennis,

Try this

Sub test()
Dim rng As Range
Dim iRow As Long
Dim i As Long

Set rng = Selection
For i = 1 To 3
iRow = Application.Match(Application.Small(rng, 1), rng, 0)
Cells(iRow, rng.Column).EntireRow.Delete
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jason Morin]

Try this in C2 and fill down:

=IF(AND(COUNTIF($B$2:B2,B2)<=1,OR(B2=SMALL($B$2:$B$11,
{1,2,3}))),"",B2)

HTH
Jason
Atlanta, GA

 

[Dennis]

Thanx Bob!! That works great for for getting rid of the three lowest entries.
What I need it to do tho is just delete the 3 entries out of the cells they
are in and leave all the rows intact. This is a great start for me tho, I'm
thinking I might need helper cells?

Thanx for the quick response Bob!!!

Dennis
=============

 

[Bob Phillips]

Hi Dennis,

How about this then, no helper cells

Sub test()
Dim rng As Range
Dim iRow As Long
Dim i As Long

Set rng = Selection
For i = 1 To 3
iRow = Application.Match(Application.Small(rng, 1), rng, 0)
Cells(iRow, rng.Column).Delete Shift:=xlUp
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dennis]

Thanx again Bob, works great!!!!!! Man you guys are good, and fast!

Dennis
==================