Match and delete rows on two different sheets from reference numbers
Hi Colin,
Am Tue, 6 Aug 2013 16:57:47 +0100 schrieb Colin Hayes:
Only rows with references 3 5 & 7 remain in sheet 1 , as these were the
only ones which match the reference numbers in sheet 2. The other rows
were deleted from sheet 1.
try:
Sub DeleteRows()
Dim LRow As Long
Dim LRow2 As Long
Dim i As Long
LRow2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = LRow To 1 Step -1
If WorksheetFunction.CountIf(Sheets("Sheet2") _
.Range("A1:A" & LRow2), .Cells(i, 1)) = 0 Then
.Rows(i).Delete
End If
Next
End With
End Sub
Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
|