I have five sheets in one document. First sheet has the rows which contains values in first column. I need to delete all those rows from the rest of the sheets which has the same value in the first column matching the first sheet first column value. The problem is its a lot of records with different column values that is supposed to be deleted. How can i just search all those rows containing the values from first sheet (or in common to the first sheet) and delete it?
Walking through each value in Sheet1's column A and using that value to filter the remaining 4 worksheets should give you the results you are looking for.
Sub remove_from_2_to_5() Dim var As Variant, w As Long, rw As Long With Sheets(1) For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row If Not IsEmpty(.Cells(rw, 1)) Then var = .Cells(rw, 1).Value For w = 2 To 5 With Sheets(w).Cells(1, 1).CurrentRegion .AutoFilter .AutoFilter field:=1, Criteria1:=var With .Offset(1, 0).Resize(.Rows.Count, .Columns.Count) If CBool(Application.Subtotal(103, .Columns(1))) Then .SpecialCells(xlCellTypeVisible).EntireRow.Delete End If End With .AutoFilter End With Next w End If Next rw End With End Sub
As the first For/Next loop goes down Sheet1's column A it records the value it finds. This is used in each of the remaining worksheets as the criteria in an
.AutoFilter operation. If there are cells showing after the filter has been applied, the rows are deleted.
Insert a column in each sheet to check whether value in the first column occurs in the first sheet:
You will get
true in the rows having first column occurring in the first sheet, filter for them and delete them.
If you have several sheets and you need even quicker solution then you need a macro.