How to remove unwanted rows on multiple worksheets

**adrianplunkett** · 09-22-2010, 07:58 AM

Hello all,

I have a large spreadsheet with multiple worksheets.

The first worksheet contains a list of study numbers (patients), and some corresponding data in columns. The list of study numbers is not consecutive - there are quite a few patients who have been removed.

The subsequent worksheets have a consecutive list, and I want to remove the numbers that aren't in the first worksheet.

I have attached a small dummy file which may make it clearer! There are 2 worksheets in the dummy file.
Thanks,

Adrian

**Domski** · 09-22-2010, 10:01 AM

Hi Adrian,

Try this:

Sub test()

Dim wsEachSheet As Worksheet
Dim wsMastSheet As Worksheet
Dim lngLastRow As Long
Dim lngLoopRow As Long
Dim rngFindRange As Range

Application.ScreenUpdating = False

Set wsMastSheet = Sheets("List with exclusions removed")

For Each wsEachSheet In ThisWorkbook.Worksheets

    If wsEachSheet.Name <> wsMastSheet.Name Then
    
        With wsEachSheet
    
            lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        
            For lngLoopRow = lngLastRow To 2 Step -1
        
                Set rngFindRange = wsMastSheet.Range("A2:A" & wsMastSheet.Cells(Rows.Count, 1) _
                    .End(xlUp).Row).Find(.Range("A" & lngLoopRow).Value, LookIn:=xlValues, lookat:=xlWhole)
    
                If rngFindRange Is Nothing Then
            
                    .Rows(lngLoopRow).Delete
                
                End If
            
            Next lngLoopRow
        
        End With
        
    End If
    
Next wsEachSheet

Application.ScreenUpdating = True

End Sub

Dom