deleting identical rows

G

Guest

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.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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."
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top