Selecting range of cells with VBA and then running a formula to delete highest number

[taichi56]

I am trying to select a range of cells C1:C5 and then run a formula that finds the highest number and then deletes that row. So far I can find the range of numbers but then the VBA coding makes the first cell a "0" and then nothing else. Eventually I want my code to go to every range and delete highest score on each team. I have attached an example of what I am working on.

[Bishonen]

Please Login or Register  to view this content.

read only thread title and assumed that you wished to delete only the highest number lol. The appropriate answer is one post below.

[AB33]

Another alternative.

Please Login or Register  to view this content.

[taichi56]

Thank you to both of you very much. They both worked but I ended up using AB33 because that deletes the whole row for me which is what I needed.

How can I get it to look at row 5 through 9 now that one is deleted from row 1 through 5 and so forth. There are five to a team when one is deleted there is 4 on the team now which is what I need. So now I have to go to the next team and take out the highest score and leave it with 4 members and then move to the next. As you see right now there are 5 member teams. When you delete one from the first team the row goes away and now the second team starts at row 5 but ends at row 9. I have to do this for 25 teams. Thank you.

[Bishonen]

here's an updated version of AB33's code

Please Login or Register  to view this content.

[taichi56]

Excellent Bishonen that worked perfectly. Also thank you AB33.

[AB33]

Bishonen! Well done!
I think I have seen this question before dressed up as football league question. Just to add flexibilty in to the code, I have changed the 100 loop in to LR and changed integers in to long. I think OP rows may go over 100. The "l" aslo looks i to my eyes(Got bad eye site), so changed it to "J".

Please Login or Register  to view this content.

[Cutter]

@ taichi56

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as an infrequent user of the forum, you may have forgotten (or may not be aware) that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.