Find & delete multiple entries in a row

bogey69 · Apr 6th 2016

ex. Find aa, ad, & bc in the below row and delete them all at once?

skywriter · Apr 6th 2016

Re: Find & delete multiple entries in a row

Upload a sample workbook.

When you click reply there will be a new button labeled go advanced, click on that button then there will be a paperclip icon in the toolbar, click on that icon and follow the instructions.

bogey69 · Apr 6th 2016

Re: Find & delete multiple entries in a row

On first sheet, "Plays Who" is where I want to find and delete all players that played with each other that day.

Example: In rows Name 2, Name 4, Name 5, and Name 8, delete BC, DK, EMo, and GR

skywriter · Apr 7th 2016

Re: Find & delete multiple entries in a row

I don't get the logic.

In your in post example you have name1, name2, etc.

In your sample workbook you have actual names and a row at the top with no name on the left but initials across the top.

Perhaps if you can further elaborate the logic in figuring out who to delete I can help you.

bogey69 · Apr 7th 2016

Re: Find & delete multiple entries in a row

Hi Skywriter,

Sorry for the confusion, I thought I would make it easier to just show name 1, 2,etc
The row on Top and Bottom are just for my reference. What I normally do is Highlight 4 players names by the row they're in and then go to Find/Replace, type in one of the 4's initials ex. (BC) and replace with nothing.
this deletes all BC's in the other 3 rows if they didn't play with BC. I then highlight another 4 players by their names and do the same Find/Replace. Allot od work that way with 40+ players.
I was looking for a faster way to delete who played with who. Find/Replace only allows you to put in one text/number value. My objective is to highlight the 4 players and put in their initials and delete them from each other, if that makes any sense to you.

skywriter · Apr 7th 2016

Re: Find & delete multiple entries in a row

If you can reference the "Plays Who" sheet you uploaded and give me a small detailed example of what you want the code to do and why?

For instance do you want to select just 2 names from column A and have something happen based on that selection and if so give me the simple steps.

I could use code for instance that if you select Bill Cannon and Bill Haverstick, the code can create initials BC and BH just by selecting these two names. I could then have the code do a find the initials and replace with empty cell, but I need to know which cells you do and don't want me looking in and why?

I'm still a little lost in what you want, so just detail it out in steps and I can probably do it for you.

Not trying to be difficult, I'm just not seeing it. Either I've had too much or too little coffee but I'm more than happy to help you if we can get on the same page.

bogey69 · Apr 8th 2016

Re: Find & delete multiple entries in a row

Hi Skywriter,

Ok, here's want I'm trying to do.
I want to select 4 names from column A, say Bill Cannon, Carl Seelye, Chuck Fereday, and Ed Moran, They all played together in the same foursome, I coded in blue their initials.
I would like to delete all the coded blue entries, this will show they played with each other. Each of the 4 rows will delete 3 players initials that are in blue

What I'm trying to accomplish is that all players get to play with all the other players, maybe I'm going about it wrong and their is a better way?
The pairings are usually in 4 somesomes. Any other ideas are most welcomed.

skywriter · Apr 8th 2016

Re: Find & delete multiple entries in a row

What to do if for instance on the row with Ed Moran only 1 or 2 of the other 3 player's initials exists.
So in stead of BC, CS and CF as you have shown only 1 or 2 of those 3 exist instead of all 3?

skywriter · Apr 8th 2016

Re: Find & delete multiple entries in a row

See post #8.
This code assumes all 3 initials are on each selected row.

Code

Sub bogey69()
Dim rngNames As Range, r As Range, arrNums(1 To 4) As Long, c As Long, j
    With Worksheets("Plays Who")
        Set rngNames = .Range("A2", .Range("A2").End(xlDown))
            For Each r In Selection
                If Intersect(r, rngNames) Is Nothing Or Selection.Count <> 4 Then
                MsgBox "Select 4 names in column A."
                Exit Sub
            Else
            c = c + 1
            arrNums(c) = r.Row
            End If
        Next r
        For Each r In Selection
            For Each j In arrNums
                If r.Row <> j Then .Cells(r.Row, j).Value = ""
            Next j
        Next r
    End With
End Sub

Display More

bogey69 · Apr 8th 2016

Re: Find & delete multiple entries in a row

Skywriter,

Most appreciate your help, as you said in #8, it gets worst when the season goes on and sometimes there's only one in a row and the other rows have 2 or 3.
At least I can use the code when all rows have 3. Thanks Again

skywriter · Apr 8th 2016

Re: Find & delete multiple entries in a row

What I meant by that is it deletes whatever is there.

I can change the code to delete nothing and alert you that not all three that you selected are there.

I could also have it just highlight those that are there and leave them in place in case that somehow helps you.

Your choice, let me know.

bogey69 · Apr 8th 2016

Re: Find & delete multiple entries in a row

Skywriter,

That code works Fantastic, as long as I pick 4 names, it doesn't matter if all 3 aren't there, that just shows the deleted ones already played together.
Can I add code to do 3 names, as I have threesomes at times? Does it have to be a separate macro? This would make it perfect.

skywriter · Apr 8th 2016

Re: Find & delete multiple entries in a row

This should work.
The other code will reset your names for you if you want to start over.

Code

Sub bogey69()
    Dim rngNames As Range, r As Range, arrNums, c As Long, j
    With Worksheets("Plays Who")
        Set rngNames = .Range("A2", .Range("A2").End(xlDown))
        ReDim arrNums(1 To Selection.Count)
        For Each r In Selection
            If Intersect(r, rngNames) Is Nothing Or Selection.Count < 3 Or Selection.Count > 4 Then
                MsgBox "Select 3 or 4 names in column A."
                Exit Sub
            Else
                c = c + 1
                arrNums(c) = r.Row
            End If
        Next r
        For Each r In Selection
            For Each j In arrNums
                If r.Row <> j Then .Cells(r.Row, j).Value = ""
            Next j
        Next r
    End With
End Sub


Sub Refill()
Dim c As Long
Application.ScreenUpdating = False
With Worksheets("Plays Who")
    .Range("B2:AS45").Value = .Range("B1:AS1").Value
        For c = 2 To 45
            .Cells(c, c).Value = ""
        Next c
End With
Application.ScreenUpdating = True
End Sub

Display More

bogey69 · Apr 8th 2016

Re: Find & delete multiple entries in a row

Skywriter,

You are Unbelievable, that code with the Refill is Triple FANTASTIC. It works perfectly. Much, Much Thanks, You made my day.

skywriter · Apr 8th 2016

Re: Find & delete multiple entries in a row

My pleasure, thanks for the feedback. :cool:

Find & delete multiple entries in a row

Participate now!

Share

Users Viewing This Thread