Macro Troubles: Remove items that are in one ws from another worksheet 1

[wraygun]

Good morning! I have two worksheets that contain customer data. I am trying to create a macro that will remove the rows that are in worksheet "Accepted" from worksheet "Registered". The two worksheets are formatted exactly the same way. Any help would be greatly appreciated. I have spent considerable time searching for a solution, but I'm not getting anywhere. If I need to post any additional info, please let me know.

Thanks so much!

Best regards,
Wray

***You can't change your past, but you can change your future***

 

[SkipVought]

What code do you have so far and where are your stuck?

As a start, try using a lookup function like MATCH, ON THE SHEET to identify what is and is not.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[wraygun]

Hi there,

Thanks for the response. I was trying to do some nested loops, but am getting errors. I haven't coded vb in years and I'm awfully rusty. Here's the broken code:

Sub FindAndDeleteBads()
Dim n&
Dim objMyFunction As Object
Dim rngMyRng1 As Range, rngMyRng2 As Range


Set objMyFunction = Application.WorksheetFunction

'Acccepted List
Set rngMyRng1 = Sheets("Accepted").Range("AL2:AL8133")

'Registered List
Set rngMyRng2 = Sheets("Registered").Range("AL2:AL18303")

'Loop for list values.
On Error Resume Next


For Each cell In rngMyRng1
cell.Value = ws1
For Each cell In rngMyRng2
cell.Value = ws2

If ws1 = ws2 Then

cell.EntireRow.Delete Shift:=xlShiftLeft

End If
End If


Next cell
Next cell

End Sub

***You can't change your past, but you can change your future***

 

[SkipVought]

You have several issues.

First, I would strongly recommend against deleting data from your sheet, the Registered, I believe. Just mark each row as having accepted. This could easily be done using spreadsheet functions.

Regarding your code. I would declare 2 additional range variables for looping, like this...

    Dim rngMyRng1 As Range, rngMyRng2 As Range, [b]r1 As Range, r2 As Range[/b]
    
    'Acccepted List
    Set rngMyRng1 = Sheets("Accepted").Range("AL2:AL8133")
    
    'Registered List
    Set rngMyRng2 = Sheets("Registered").Range("AL2:AL18303")
    
    'Loop for list values.
    
    For Each r1 In rngMyRng1
        For Each r2 In rngMyRng2
        
            If r1.Value = r2.Value Then
            
[b][red]
 'BUT DELETING like this is a HUGE problem.  When you delete, you DESTROY the r2 object and now the loop is BROKEN!!!               
[/red][/b]            
            End If
        
        Next cell
    Next cell

So when you delete rows, it is best to loop from BOTTOM to TOP, using a Row Number value like...

for lRowNbr = LastRow to FirstRow Step -1

next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[wraygun]

Hi Skip,

Thanks so much. I'm running this as is just to make sure it iterates through all the records (without marking or deleting). This process is taking a while. As an alternative, is there an easy way just to insert the records into a new worksheet rather than going through the trouble of deleting?

Thank you very much. I'm eternally grateful.

-Wray

***You can't change your past, but you can change your future***

 

[SkipVought]

Which ones do you want to insert?

You could COPY 'n' PASTE.

If not a one-time thing...

You could use a PivotTable, although you ALSO get aggregations.

You could use a Query. For instance, mark the registrations with an accepted date. Then query the rows Where [Accepted Date] Is Not Null
for your Accepted sheet.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[wraygun]

Hi Skip,

Thank you so much for your guidance. I was able to muddle through the rest and get a working solution.

Best regards,
Wray

***You can't change your past, but you can change your future***