PDA

View Full Version : Solved: Delete Rows with same information



parttime_guy
03-26-2007, 07:41 PM
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.

Biz
03-26-2007, 08:54 PM
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,
Try the attachment. Click on Column A and then run the macro. Is this what you are after?

Biz

johnske
03-26-2007, 11:00 PM
Try...
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

parttime_guy
03-27-2007, 07:16 PM
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
:friends:

johnske
03-27-2007, 08:22 PM
...The macro filters all the duplicates & reverts back to to its original self - does not delete the duplicates?...I find that very strange, as it deletes all the duplicate rows on my machine. What office version are you using?

mdmackillop
03-28-2007, 10:36 AM
Hi John,
I suspect with 30k rows, the range is too complex.

PartTimeGuy,
Give this (http://members.iinet.net.au/~brettdj/) a try, created by one of our members. I use it regularly

parttime_guy
03-28-2007, 07:58 PM
Yo! :super: 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
:clap2:

nserrano
07-16-2007, 08:10 AM
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.

lucas
07-16-2007, 08:45 AM
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.