0

I have a spreadsheet were I have multiple ID duplicates. I need to extract specific duplicates based on a string in the column next to the ID using a vba code. For example:

Letter: t f t t t f f f                
ID:     1 1 2 3 3 3 4 4

I need to extract the duplicates that have the letter t with number 3, letter t number 1, and both f with number 4.

I've created the code below but its only pulling 1 of the duplicates. 

Sub transfer_dups()
    Sheets("OP").Activate
    Dim OP As Worksheet
    Dim Final As Worksheet
    Dim lr As Integer  'lr = last row. Wanted to make it different from other parts of the code so there is not complications
    Dim i As Integer 'This is working as my row counter for the forloop

    'The code below will ensure that the data that was preciously populating the final filter sheet is cleared for a new process
    Sheets("Final").Select
    Range("A1:AQ300").ClearContents

    'Searching and selecting in the OP sheet (Opportunities)
    Sheets("OP").Select
    lr = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To lr
        'if CELL B2=B3 and cell A2<>A3 then 
        If Cells(i, 8) = Cells(i + 1, 8) And Cells(i, 7) <> Cells(i + 1, 7) Then
            Range(Cells(i, 1), Cells(i, 48)).Copy 'This range may change if more columns are added
            Sheets("Final").Select
            Range("A300").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats 'Find the next available row
            Sheets("OP").Select

Thank you in advance for the help!

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
kiara
  • 1
  • 1
    Notes: I highly recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • Also you cannot use `Integer` for row counting variables. Excel has more rows than `Integer` can handle. Always use `Long` instead: `Dim lr As Long` – Pᴇʜ Jan 07 '19 at 15:51
  • 1
    Where's the actual dupe checking code? – Nathan_Sav Jan 07 '19 at 15:52
  • 1
    Where's the rest of your code? Looks like it was cut right in half. – dwirony Jan 07 '19 at 17:35

0 Answers0