0

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?

  • Can it be reasonably assumes that A1 in all of the worksheets contains a header column label that should be left alone? If so, filtering for each value in Sheet1's column A and deleting would be the best solution. – Jeeped Apr 19 '15 at 19:12
0

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.

  • This deletes all the records from all the sheets :( – Shaonline Apr 20 '15 at 11:44
  • @Shaonline - During my tests, it only deleted the data that was duplicated from column A on sheet1. I'll have to see some redacted data on multiple sheets that reproduces the actions you describe. – Jeeped Apr 20 '15 at 13:55
0

Insert a column in each sheet to check whether value in the first column occurs in the first sheet: =iferror(match(a2,sheet1!a:a,0)>0,false)
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.

  • I am using the following formula now =iferror(match(a2,ti!a:a,0)>0,false) in rest of the sheets by adding a new column but it gives me true for all the values even those which do not match. I am replacing sheet1 with "ti", which is the name of my first sheet. What am i doing wrong? – Shaonline Apr 20 '15 at 9:43
  • it's strange, do you have automatic calculations enabled in your workbook? – Máté Juhász Apr 20 '15 at 9:45
  • Yes it is... do i need to turn it manual? – Shaonline Apr 20 '15 at 11:22
  • it's fine. Can you post some example data? It should work well. – Máté Juhász Apr 20 '15 at 11:50
  • Reading your comment to the other answer it seems there is something odd with your data, please check whether you have any data shouldn't be deleted! – Máté Juhász Apr 20 '15 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.