Duplicate Rows should be deleted as they duplicates and match all the criterias in the columns.
I have tried Advanced Filter with Unique Records the file is to big it hangs sometimes.
Duplicate Rows should be deleted as they duplicates and match all the criterias in the columns.
I have tried Advanced Filter with Unique Records the file is to big it hangs sometimes.
Hi,Originally Posted by parttime_guy
Try the attachment. Click on Column A and then run the macro. Is this what you are after?
Biz
Try...
[vba]Option Explicit
'
Sub DeleteDuplicateRows()
'
Dim Cell As Range, RowsToDelete As String
'
Application.ScreenUpdating = False
With Sheet1 '< put your sheet name here
On Error Resume Next
.ShowAllData
.UsedRange.AdvancedFilter Action:=xlFilterInPlace, unique:=True
'change "A" below if there is no column A - a heading is assumed
For Each Cell In .Range("A2", .Range("A" & .UsedRange.Rows.Count + 1))
If Cell.EntireRow.Hidden Then
RowsToDelete = RowsToDelete & Cell.Row & ":" & Cell.Row & ","
End If
Next
.Range(Left(RowsToDelete, Len(RowsToDelete) - 1)).Delete
.ShowAllData
End With
Application.ScreenUpdating = True
'
End Sub[/vba]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Yo! Biz & Guru
I have checked both the codes,
Biz code:
Works fine with the small file that I have posted.
Guru code:
The macro filters all the duplicates & reverts back to to its original self - does not delete the duplicates?
I will get back shortly after checking Biz code on my file which has about 20 columns & more than 30,000 rows (with duplicates).
Thx-n-BR
I find that very strange, as it deletes all the duplicate rows on my machine. What office version are you using?
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Hi John,
I suspect with 30k rows, the range is too complex.
PartTimeGuy,
Give this a try, created by one of our members. I use it regularly
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Yo!Everybody
mdmackillop u have just made my day, this is just what I needed. I thing this file or link should be in the kb - it just does everything u need to do with duplicates.
Thx for all ur help
Best regards
I have a list of employees that have their sign-in time stamped. I want to be able to delete the duplicate timestamps and paste the results to their individual worksheets. I will be doing this everyweek so each copied range will need to start on the next available row in their worksheet. Any idea how to ? Thanks in advanced.
nserrano,
This seems to be more than just a followup question for this thread so I would suggest that you start a new thread. It would help if you would post an example with some detail of what you are trying to do.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Posting Permissions
Reply With Quote
