asked on

VBA Macro to find duplicates and copy rows

Hello,

I am trying to make a macro that checks the active sheet for duplicates in column C and if so copy the original and new rows with the duplicate fields to "seet2" and continue on. I am having a bit of trouble getting it going mostly because of my lack of knowledge of the structure of the objects etc.

Here is what I have but is not working:

Sub Macro1()

    Dim ws, ws1 As Range
    Dim iMatches, iIsdup As Integer
    
    Set ws = Range("A1:Z3000")
    Set ws1 = Range("A1:Z3000")
    
    iMatches = 1
    
    For Each Row In ws.Rows
        If IsEmpty(ws.Cells(Row.Row, 1)) Then
            Exit For
        Else
            For Each row1 In ws1.Rows
                If IsEmpty(ws1.Cells(row1.Row, 1)) Then
                    Exit For
                Else
                    If ws1.Cells(row1.Row, 1).Value = ws.Cells(Row.Row, 1).Value Then
                        Worksheets("Sheet2").Rows(iMatches & ":" & iMatches) = row1
                        iMatches = iMatches + 1
                        iIsdup = 1
                    End If
                End If
            Next
        End If
    Next

End Sub

Open in new window

Any help is appreciated. I know I am going about this all wrong programatically I am mostly trying to familiarize myself with the excel vba objects. If this should be scrapped for a better approach then I am all ears.

StephenJR

Could you perhaps post a small sample workbook so we can see if there might be a better approach?

jason987

ASKER

Sure I colored in the cells that are being evaluated/duplicates:

Book1---Copy.xls

jason987

ASKER

Sheet2 shows the desired output.

ASKER CERTIFIED SOLUTION

StephenJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jason987

ASKER

Arrggh dog should be copied/isduplicate too.

StephenJR

OK, well try the code on your example and it should work.

jason987

ASKER

Your code returns true for the if statement but it doesn't seem to copy out the rows.

StephenJR

I tested it on your file and it worked.

jason987

ASKER

Oops, my bad I had another workbook open that it was modifying.

Thanks for the code. It works although, I was hoping to deal with the rows/values on a more individual basis. Like I was hoping to manipulate the values by removing double spaces and redundant words. I know how to do all of the string/value functions I am just a bit low on the knowledge of the kind of excel vba shorthand you have there.

jason987

ASKER

Maybe it's just time for me to properly learn excel vba object model ;)

StephenJR

That sounds like a different question. You can use the Trim function to remove spaces, but you would have to specify what is redundant. But as I say, that is another question.