Copy entire row where Duplicates are found in Column B

Hi,

I am looking for a script which can look at column B in a report - Identify the duplicates and copy the entire row (Or the range A:AZ) to a new workbook. I've tried to do this with a loop, however, with 50,000 rows it goes extremely slow and the script crashes my excel. Any help would be greatly appreciated...

mehmetcik,

Thanks for the reply, however, I'm looking to keep this as a Run the macro to execute the task without any manual work being done...

Before to look for a script just well think about what Excel offers to achieve your need 'cause here yes looping is not a great idea
as any Excel beginner does not need a code and can operate manually in few seconds just using an Excel basics : an advanced filter !
If really really he needs a code he can activate the Macro Recorder and operate manually in order to get his own code base …

According to your attachment as a VBA beginner starter :

PHP Code: 

Sub Demo1()
         Application.ScreenUpdating = False
         Workbooks.Add
    With Sheet1.UsedRange
        .Range("BB2").Formula = "=COUNTIF(" & .Columns(2).Address & ",B2)>1"
        .AdvancedFilter xlFilterCopy, .Range("BB1:BB2"), [A1]
        .Range("BB2").Clear
    End With
         Application.ScreenUpdating = True
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

mehmetcik,

Appreciate the quick response, the code you wrote works very well - One issue I ran into when running this on sample set is if there are no duplicates, the code bombs out. Is there a way to add a catch where if no duplicates are found the new workbook is not created?

Marc,

This works very well and does not error out when no duplicates were found... Is there any way to add a line of code where if no duplicates were found it wont create the workbook? Keep one thing in mind - I cannot modify the original document - its a master list of sorts and cannot be modified by adding or removing columns....

After End With just insert this codeline : If IsEmpty([B2]) Then ActiveWorkbook.Close False …

Marc,

Thank you, that worked very well, much appreciated!