find matching cells on two different sheets

[aem172]

Hi,

Here is the situation:

I have two sheets in a workbook. On sheet 1 in columns A,B,C I have information. On sheet 2 in columns A,B,C I also have information. In column C on both sheets are numbers similar to SSN. I need a macro that will look at the values in column C on sheet 1 and then go to sheet 2 and find the exact number also in column C. Then, once the number is found, I need the macro to delete the entire row the number is in. Any help would be greatly appreciated. I am very new to macros and have no idea where to start. Thanks!

 

[Damon Ostrander]

Hi aem172,

Here is a macro that I believe does what you want. Give it a try.

Sub DelCommonRows()

   'Deletes rows in Col C or Sheet2 with values
   'matching values in Col C of Sheet1
   
   Dim Sh1     As Worksheet: Set Sh1 = Worksheets("Sheet1")
   Dim Sh2     As Worksheet: Set Sh2 = Worksheets("Sheet2")
   
   Dim iRow1   As Long  'row number in sheet1
   Dim Cell    As Range
   
   'Loop through all rows containing data in col C of Sheet1
   'Skip row 1 (assuming it is a header row)
   For iRow1 = 2 To Sh1.Range("C65536").End(xlUp).Row
      If Not IsEmpty(Sh1.Cells(iRow1, "C")) Then
         'look for matching cell value in col C of sheet 2
         Do
            Set Cell = Sh2.Range("C:C").Find(what:=Sh1.Cells(iRow1, "C").Value)
            If Not Cell Is Nothing Then Cell.EntireRow.Delete
         Loop Until Cell Is Nothing
      End If
   Next iRow1
   
End Sub

If your sheets are not named "Sheet1" and "Sheet2" simply change these two text strings in the code (one place for each).

This code should be placed in a standard macro module. To do this go to the VBE (keyboard Alt-TMV), insert a new macro module (Alt-IM) and paste this code into the Code pane. To run the macro go back to Excel and Alt-TMM.

Also, note that the code assumes that row 1 on sheet1 is a header row (the iRow1 loop starts with 2).

Keep Excelling.

Damon