Deleting rows in one sheet based on values from another

[strobopop]

Hi,

I am an absolute excel newbie and desperately need your help. I have a dataset with 2 sheets. Sheet A contains information on 2000 subjects, but Sheet B provides additional info on 500 of those 2000.

In both sheets, column A provides the identification number for each subject. Now, I want to delete all the subjects on Sheet A that don't have additional info on Sheet B using the ID number in column A of each sheet. So what do I got to do?

Any help would be greatly appreciated!

 

[Fluff]

Hi & welcome to the board.
Untested, but try

Sub DeleteRows()

   Dim Cl As Range
   Dim Rng As Range
   Dim WsA As Worksheet
   Dim WsB As Worksheet
   
   Set WsA = Sheets("[COLOR=#ff0000]A[/COLOR]")
   Set WsB = Sheets("[COLOR=#ff0000]B[/COLOR]")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In WsB.Range("A2", WsB.Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Nothing
      Next Cl
      For Each Cl In WsA.Range("A2", WsA.Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            If Rng Is Nothing Then
               Set Rng = Cl
            Else
               Set Rng = Union(Rng, Cl)
            End If
         End If
      Next Cl
   End With
   If Not Rng Is Nothing Then Rng.EntireRow.Delete
   
End Sub

Change sheet names in red to suit

 

[strobopop]

Hi, thank you for welcoming me. Its a pleasure to e-meet you.

I assume thats VBA code? I get the "Runtime Error 429" when trying to run that code. I am using excel 360 on a macbook, if that makes any difference.

 

[Fluff]

When using a Mac it's always best to say so, as VBA for Mac is not quite the same as for a PC. AFAIK you cannot use dictionaries on a Mac & as I don't have a Mac I'm afraid that I cannot help any further.

 

[strobopop]

Nevertheless, thank you for the help. Someone here familiar with Mac ?