UserForm to delete unwated items off sheet?

[Sue]

Hi All

We are an Angling Club and when we have Matches the entrants can enter
various Pools
which are listed on the entry form as A,B,C,D,E,F,G - on a UserForm I enter
all the entrants
details name etc plus what pools they have entered in Textboxes the pools
are entered in
columns K : Q starting at row 6 e.g. J. Bloggs A,B,C, - next row -- J. Smith
A,B,C,D, etc -- an Angling Match
is decided by the weight caught by each individual Angler the weights are
sorted in column J
highest to lowest. We only payout 3 prizes in each pool 50%,30%,20% -- my
problem is that
on the sheet there will be many A,B,C,D,E,F,G entries some with blank cells
in between in each of the columns
and all I need is the first 3 A's B's etc and I need all the others deleted
is it possible that someone can
help with a macro that would do this for me.

 

[Mike H.]

So if J Smith enters Pools A, B, C AND D only, for example, can he have more
than one fish caught in each pool? How do you determine which pool to put
which fish? Not sure I understand how you enter the information. But to
make sure I understand what you desire. You want to know the top 3 heaviest
fish in each pool, right?

 

[Joel]

I used column J to determine where the last row is. The code checks columns
K thru Q and removes any entry after the third item on each row.


Sub clearcolumns()

LastRow = Range("J" & Rows.Count).End(xlUp).Row
StartCol = Range("K6").Column
EndCol = Range("Q6").Column
For RowCount = 6 To LastRow
Count = 0
For ColCount = StartCol To EndCol
If Cells(RowCount, ColCount) <> "" Then
If Count > 3 Then
Cells(RowCount, ColCount) = ""
Else
Count = Count + 1
End If
End If
Next ColCount
Next RowCount
End Sub

 

[Sue]

--
Many Thanks

Sue

So if J Smith enters Pools A, B, C AND D only, for example, can he have more
than one fish caught in each pool? How do you determine which pool to put
which fish? Not sure I understand how you enter the information. But to
make sure I understand what you desire. You want to know the top 3 heaviest
fish in each pool, right?

Hi Mike

Thanks for prompt reply

I know its not easy to explain so If I just use column "K" and the letter
"A" J. Bloggs has weighed in his catch he maybe the 21st out of 60 to weigh
in and he has pooled in "A" when sorted in Column "J" he is 1st so he gets
50% J.Smith when he weighs in his fish he might be 33rd out of 60 and when
sorted he ends up in 9th position overall however nobody between 2nd & 8th is
in pool "A" so he would draw 30% & ANother is 12th overall 10th and 11th are
not in pool "A" so ANother draws 20%. All other "A's" in column "K" would be
deleted because all the Prize Money in pool "A" has been allocated and so on
through columns L to Q because using letters. I found the code below in this
forum that puts the money totals where "A" is and I adapted it for each
Column on separate commandbuttons.



Sub Add500_Click()

Dim v(1 To 3), rng As Range, rng1 As Range
Dim sAddr As String, ii As Long
Set rng = Worksheets("InputData").Range("K6:K40")
Set rng1 = rng.Find(What:="A", _
After:=rng(rng.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng1 Is Nothing Then
v(1) = Evaluate(Me.Tb601.Text)
v(2) = Evaluate(Me.Tb602.Text)
v(3) = Evaluate(Me.Tb603.Text)
ii = 1
sAddr = rng1.Address
Do
rng1.Offset(0, 0).Value = Application.Large(v, ii)
Set rng1 = rng.FindNext(rng1)
ii = ii + 1
Loop Until rng.Address = sAddr Or ii > 3
Else
MsgBox Range("K1").Value & " was not found"
End If

End Sub

 

[Sue]

Hi Joel

Tried very hard to get your code working it is only clearing the contents of
columns "O,P,Q" and removing everything in those columns and I had to put a
letter in every cell it would not run at all if there are any blank cells in
the column.
Any idea's much appreciated