DEL ROW in SHEET2 if COL(A) is same in SHEET1

Microsoft Microsoft excel 2007 full vers...
April 2, 2010 at 11:31:28
Specs: Windows Vista
I have an Excel Workbook with 2 sheets of idenitcal format. I want Sheet 1 to act as a control to run against Sheet2. If Sheet 2 has any cells in COL A that mathces any cells in Sheet1 COL A - I want to detete it. I need to ensure that I have only unique values (based on COL A).

This is two sheets of several contacts. Collumn A is the unique idenitfier. Sheet 1 contains contacts that have already been processed. I want to pull these out of Sheet 2 before that sheet is processed.

Can anyone help me?

See More: DEL ROW in SHEET2 if COL(A) is same in SHEET1

Report •

April 2, 2010 at 13:57:24
The trick to deleting Rows via a For-Next loop in VBA is to start at the bottom and work your way up. If you start at the top and work down, the loop "loses count" as the Rows are deleted.

Option Explicit
Sub DelRows()
Dim sht1Rows, sht2Rows, DelRow
Dim c
'Find last Row on Sheet 1
  sht1Rows = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Find last Row in Sheet 2
  sht2Rows = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
'Loop "up" through Sheet 2 Rows looking at Sheet 1 for matches
   For DelRow = sht2Rows To 1 Step -1
    With Sheets(1).Range("A1:A" & sht1Rows)
     Set c = .Find(Sheets(2).Range("A" & DelRow), lookat:=xlWhole)
'If a match is found, delete the Row from Shet 2
       If Not c Is Nothing Then _
         Sheets(2).Range("A" & DelRow).EntireRow.Delete
    End With
End Sub

Report •

April 2, 2010 at 14:56:40
Thanks a million! That did it!

Report •

Related Solutions

Ask Question