deleting identical rows

[Guest]

hi,

Is it possible to delete identical rows in a sheet. (each row has about 10 cells of information: if all cells are identical I want to delete the row) I want to build a macro which does this everytime a user closes the sheet. (so on workbook_beforeclose)

Hope anyway can help me on the way.

Thnks!

Remi
Holland.

 

[Legacy 344]

Do you want to delete a row if it is the same as another row?
Or do you want to delete a row if all the data in each cell of that row is the same?

 

[Guest]

The second one you suggest.

Would you know a solution?
thnks!

 

[Legacy 344]

Just to clarify what you want:- If the contents in each of the cells in A3:J3(for example) are the same, row 3 should be deleted.

Try the following. It is assumed that the data in all rows start in column A

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh As Worksheet
Dim rng1 As Range, rng2 As Range, cell As Range
Dim cCount%, Lc%, x%, toDelete As Range
Set sh = Worksheets("Sheet1") 'Change sheet name as necessary
Application.EnableEvents = False
Application.ScreenUpdating = False
Set rng1 = Intersect(sh.UsedRange, Columns(1))
For Each cell In rng1
Lc = Cells(cell.Row, 256).End(xlToLeft).Column
Set rng2 = Range(Cells(cell.Row, 1), Cells(cell.Row, Lc))
cCount = rng2.Cells.Count
If Application.WorksheetFunction.CountIf(rng2, cell) = cCount Then
If x = 1 Then
Set toDelete = Union(toDelete, cell)
Else
Set toDelete = cell
x = 1
End If
End If
Next
toDelete.EntireRow.Delete
ActiveWorkbook.Save
Application.EnableEvents = True
End Sub

 

[Juan Pablo González]

There's no need to do a macro here. You can use Excel's built in features that work much better !

Select the data, go to Data, Filter, Advanced Filter. In there, check "Unique Records Only" and you have two choices, you can copy the results to someplace else or you can just click Ok to show the results in the same location.

 

[Legacy 344]

On 2002-02-27 05:45, Juan Pablo G. wrote:
There's no need to do a macro here. You can use Excel's built in features that work much better !

Select the data, go to Data, Filter, Advanced Filter. In there, check "Unique Records Only" and you have two choices, you can copy the results to someplace else or you can just click Ok to show the results in the same location.

But that's not what the poster wants to do.

 

[Juan Pablo González]

Then record a macro to do the ADvanced Filter... I bet it's just "a little" faster.

 

[Guest]

On 2002-02-25 01:16, Autolycus wrote:
Do you want to delete a row if it is the same as another row?
Or do you want to delete a row if all the data in each cell of that row is the same?

Can you give me a macro for the first problem?(delete a row if it is the same as another rouw?Many thanks

 

[Legacy 344]

On 2002-02-27 07:07, Juan Pablo G. wrote:
Then record a macro to do the ADvanced Filter... I bet it's just "a little" faster.

A little faster than what? The macro I posted does not do the same thing as retaining only unique records.
I asked the poster(or at least thought I had asked) if he wanted to retain unique records and he replied no.
Obviously my question was not phrased very well because he is now asking for a macro to do that.
This message was edited by Autolycus on 2002-02-27 14:44

 

[Legacy 344]

On 2002-02-27 07:07, Anonymous wrote:

On 2002-02-25 01:16, Autolycus wrote:
Do you want to delete a row if it is the same as another row?
Or do you want to delete a row if all the data in each cell of that row is the same?

Can you give me a macro for the first problem?(delete a row if it is the same as another rouw?Many thanks

Record a macro as suggested by Juan Pablo G :-
"Select the data, go to Data, Filter, Advanced Filter. In there, check "Unique Records Only" and you have two choices, you can copy the results to someplace else or you can just click Ok to show the results in the same location."