Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm to delete unwated items off sheet?
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. -- Many Thanks Sue |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm to delete unwated items off sheet?
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm to delete unwated items off sheet?
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" wrote: 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. -- Many Thanks Sue |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm to delete unwated items off sheet?
-- Many Thanks Sue "Mike H." wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm to delete unwated items off sheet?
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 -- Many Thanks Sue "Joel" wrote: 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" wrote: 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. -- Many Thanks Sue |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete items | Excel Discussion (Misc queries) | |||
Listbox has some items selected when userform containing it opens. | Excel Programming | |||
userform delete sheet | Excel Programming | |||
userform delete sheet | Excel Programming | |||
Deleting unwated control characters at the end in a particular column | Excel Programming |