deleting rows depending on values on another sheet

**ghynes** · 01-19-2013, 01:34 PM

hello all.

i am using excel database (see attachemet) to list player games vs one another. In sheet 1 listed in column A and C are players 1 and 2 games (eg A1 vs C1).
On sheet 2 a certain group are allocated points on previous match history. I am looking to write code to delete players games from sheet 1 (delete entire row) depending on points they have on sheet 2. Firstly I want to find the golden points tally that will remove them from games in sheet 1. The golden points tally will calculated by the player that is top of the last quarter of teams (which would be Lucas on 8 points) and i want to add 3 on to this which would bring golden points to 11.. and basically delete anyone, that has 11 points or less in the group, from the games on sheet1.
The results of the script would be that games involving Loren, Margarett, Lynn, Luciano, Lucas, Erwin, Derek, and Davina should all be deleted from sheet 1 because they have only 11 or less points.
Anyone assist with this? Thanks.

**Mumps1** · 01-20-2013, 10:47 AM

Will there always be 20 players in column A on sheet 2 or can this number vary?

**ghynes** · 01-31-2013, 08:14 PM

Ok. that way of removing matches is not going to work. i need to do it another way. On the attached workbook (player games2) there is a list of matches in sheet1. In sheet 2 is the table.
I am looking for a way to cycle through all the games in sheet1 and remove the games if the players are side by side in the table.
for eg.. Abraham Vs Zack match should be removed because they are 6th and 7th.
And Alfonso Vs Jolanda match should be removed because they are 4th and 3rd. any ideas?
Player games2.xlsx

**Trebor76** · 01-31-2013, 10:47 PM

Hi ghynes,

Assuming that Ali Vs Jonathan should also be deleted as they are 8th and 9th, the following will do the trick:

Option Explicit
Sub Macro2()

    'Written by Trebor76
    'Visit my website www.excelguru.net.au
    
    Dim lngStartRow As Long, _
        lngMyCol As Long, _
        lngMyRow As Long, _
        lngEndRow As Long
    
    lngStartRow = 2 'Starting row number for the data. Change to suit.
        
    lngMyCol = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    lngMyRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lngEndRow = Sheets("Sheet2").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        
    Application.ScreenUpdating = False
    
    With Sheets("Sheet1").Columns(lngMyCol)
        With Range(Sheets("Sheet1").Cells(lngStartRow, lngMyCol), Sheets("Sheet1").Cells(lngMyRow, lngMyCol))
            .Formula = "=IF(ABS(MATCH(A2,Sheet2!$A$2:$A$" & lngEndRow & ",0)-MATCH(C2,Sheet2!$A$2:$A$" & lngEndRow & ",0))=1,""DEL"","""")"
            .Value = .Value
        End With
        .Replace "DEL", "#N/A", xlWhole
        On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message
            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
        On Error GoTo 0 'Turn error reporting back on
        .Delete
    End With
    
    Application.ScreenUpdating = True
    
    MsgBox "All applicable rows have now been deleted."

End Sub

Not too sure what will happen when the player list expands as the MATCH function works off the first match found

Regards,

Robert

**ghynes** · 01-31-2013, 11:45 PM

Super stuff Robert. Trying to make a few additions to it. I have added ad-hoc games to the sheet 1 with different players names that are not in the league table and these get an #N/A beside them too and end up getting deleted... i presume its because they are not found on the league table?

**ghynes** · 01-31-2013, 11:56 PM

I have added the line of code thats bolded below and it seems to do the trick..

        
.Replace "#N/A", "OK", xlWhole
.Replace "DEL", "#N/A", xlWhole

**Trebor76** · 02-01-2013, 02:22 AM

i presume its because they are not found on the league table

Yes, that's why they're being deleted. To be consistent with the formula the code puts in, I'd have it like this:

.Replace "#N/A", "", xlWhole

But that's being really picky!!

If your issue is solved you should mark the thread as so.

Kind regards,

Robert

**ghynes** · 02-01-2013, 04:01 PM

Top stuff. Thanks again.

**ghynes** · 02-06-2013, 03:26 PM

Hey Robert,

Trying to run the code of every worksheet in the workbook.. is it possible to use ws in the code like below..? is it not working for me.
I have everything set up correctly for the ws but dont know how to use it in a formula.

Original:

      .Formula = "=IF(ABS(MATCH(A2,Sheet2!$A$2:$A$" & lngEndRow & ",0)-MATCH(C2,Sheet2!$A$2:$A$" & lngEndRow & ",0))=1,""DEL"","""")"

Edited with ws:

      .Formula = "=IF(ABS(MATCH(A2,ws.Name!$A$2:$A$" & lngEndRow & ",0)-MATCH(C2,ws,Name!$A$2:$A$" & lngEndRow & ",0))=1,""DEL"","""")"

**ghynes** · 02-07-2013, 07:08 PM

Originally Posted by ghynes

Hey Robert,

Trying to run the code of every worksheet in the workbook.. is it possible to use ws in the code like below..? is it not working for me.
I have everything set up correctly for the ws but dont know how to use it in a formula.

Original:

      .Formula = "=IF(ABS(MATCH(A2,Sheet2!$A$2:$A$" & lngEndRow & ",0)-MATCH(C2,Sheet2!$A$2:$A$" & lngEndRow & ",0))=1,""DEL"","""")"

Edited with ws:

      .Formula = "=IF(ABS(MATCH(A2,ws.Name!$A$2:$A$" & lngEndRow & ",0)-MATCH(C2,ws,Name!$A$2:$A$" & lngEndRow & ",0))=1,""DEL"","""")"

I managed to get this sorted by declaring sheetname as string and using it that way.

**ghynes** · 02-07-2013, 07:16 PM

Have yet another addition I want to make to the macro and wondering the best way to do it! With the player games2 file above.. I am looking to check where the players are in the table points wise, and if the players are 10 or less points away from one another, the game must be removed from sheet1. for eg... Abraham Vs Zack must be removed because they are separated by 1 point, Alfonso Vs Jolanda must be removed because they are separated by 10 points, Ali Vs Jonathan must be removed because they are separated by 9 points. Any suggestions?

deleting rows depending on values on another sheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

deleting rows depending on values on another sheet

Re: deleting rows depending on values on another sheet

Re: deleting rows depending on values on another sheet

Re: deleting rows depending on values on another sheet

Re: deleting rows depending on values on another sheet

Re: deleting rows depending on values on another sheet

Re: deleting rows depending on values on another sheet

Re: deleting rows depending on values on another sheet

Re: deleting rows depending on values on another sheet

Re: deleting rows depending on values on another sheet

Re: deleting rows depending on values on another sheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions