Macro to check Duplications

[cstahlhut]

Hi all,

I'm new to the forum and looking for a little help. I have a spreadsheet that looks like this: -

Company Name

Sub Classification

Position

Under company name is the list of a bunch of different companies, what I want to is have a seperate master worksheet that I can add the names of companies too. If the master list contains the company name I want it to remove the row from the orginial spreadsheet.

I.E. If Masterspreadsheet Column A contains "Ambition Technology" and the orginial spreadsheet has 5 rows that contain "Ambition Technology" those rows will be deleted.

Is anyone able to help me please? I tried to figure it out myself by looking around online but quickly got confused!

Thanks in advance,
Cheyne

[rscsmith]

Hi Cheyne

Welcome to the forum.

Are you able to upload some sample data so we can see what it looks like? To do this, hit Go Advanced at the bottom, then Manage Attachements.

It doesn't sound like that difficult a task, but we will need to know sheet names, etc. Also, I'm not clear whether you have two sheets in one file, or two separate files.

Remember to delete any confidential data before you upload!

Thanks, Rob.

[cstahlhut]

Masterfile - Delete these.xlsxsample data.xlsx

Thanks Rob,

I've attached two sample files, so in this case for instance the "Masterfile - Delete these.xlsx" contains the names of the companies I would like removed from the other file "Sample Data.xlsx"

So if the macro is working correctly it should use the names in the Masterfile spreadsheet to delete the matches in the sample data speadsheet on both worksheets, my orginial data contains approx 19 worksheets in the one spreadsheet and a hell of a lot more data, haha.

So if it works on the Architects worksheet in the sample data spreadsheet the only entries left should be "Company B" and on Business_Systems_Analysts the only entry left should be "Company B" also.

Thanks!

[cstahlhut]

Anyone able to help me with this, I've tried a couple of different macros I found on the net but can't get them to do what I want

[jindon]

Try
Assuming both files are in the same folder.
Run from "Sample data.xlsx"

Sub test()
    Dim a, i As Long, ws As Worksheet, r As Range, x As Range, ff As String
    With Workbooks.Open(ThisWorkbook.Path & "\" & "Masterfile - Delete these.xlsx")
        a = .Sheets(1).Range("a1").CurrentRegion.Value
        .Close False
    End With
    For Each ws In Worksheets
        For i = 2 To UBound(a, 1)
            Set r = ws.Columns(3).Find(a(i, 1), , , xlWhole)
            If Not r Is Nothing Then
                ff = r.Address
                Set x = r.EntireRow
                Do
                    Set x = Union(x, r.EntireRow)
                    Set r = ws.Columns(3).FindNext(r)
                Loop Until ff = r.Address
                x.Delete
                Set x = Nothing
            End If
        Next
    Next
End Sub

[cstahlhut]

Thanks very much Jindon, that worked great!

[jindon]

You are welcome and thanks for the feedback.