Deleting/Clearing duplicate range of cells

[Steve]

Below is my data.
If a row range of cells is identical to another, could a macro be written
to find the duplicated rows, and clear those cells ?
As follows:
M3:R3 no duplicate, so keep M3:R3
M4:R4 identical to M5:R5, so delete/clear M5:R5
M6:R6 identical to M7:R7, so delete/clear M7:R7
M8:R8 identical to M9:R9, so delete/clear M9:R9
M10:R10 identical to M11:R11 & M12:R12, so delete/clear M11:R12
There will be no consistency as to what rows would be duplicated and
therefore needing to be cleared, meaning tomorrow it might be row 4 that is a
duplicate to 3, so 4 would need to be cleared; 5, 6, 7 & 8 might be
identical, so 5, 6 & 7 would need to be cleared, etc.

Row M N O P Q R
3 90 100 580 3 9000 2010042
4 90 1000 191 6 9100 2010041
5 90 1000 191 6 9100 2010041
6 90 1000 191 6 9100 2010042
7 90 1000 191 6 9100 2010042
8 90 1000 191 6 9100 2010062
9 90 1000 191 6 9100 2010062
10 90 1000 191 9 9100 2010071
11 90 1000 191 9 9100 2010071
12 90 1000 191 9 9100 2010071

Thank you,

Steve

 

[p45cal]

1. When you say clear/delete, do you want just to render the cells i
that row empty, or delete the row and close up the gap?
2. If closing up the gap, is it safe to delete the entire row, righ
across the sheet, or do you need it to just close up the gap in column
M:R?

 

[Steve]

Just delete the duplicated data in M:R.

A:L has data I'll still need.

 

[p45cal]

..and is the range always fully sorted so that duplicates are guaranteed
to be next to each other?

 

[Steve]

yes, they'll always be next to each other.

 

[p45cal]

assuming the the last of the rows involved can be reliably ascertained
from selecting M3 then pressing End, then the down button (you don't
have to do this, it's just the technique the macro uses to identify the
rows involved) then this code should do it. It assumes the correct sheet
is active:


VBA Code:
--------------------


Sub blah()
Dim xxx As Range
Set xxx = Range(Range("M3"), Range("M3").End(xlDown).Offset(, 5))
With xxx
For i = xxx.Rows.Count To 2 Step -1
.Rows(i).Select
Different = False
For Each cll In .Rows(i).Cells
cll.Select
If cll <> cll.Offset(-1) Then
Different = True
Exit For
End If
Next cll
If Not Different Then .Rows(i).Clear
Next i
End With
End Sub
--------------------






yes, they'll always be next to each other.Code Cage Forums'
(http://www.thecodecage.com/forumz/showthread.php?t=199126)

 

[Steve]

That is nothing short of amazing. THANK YOU very much.

As you probably ascertained, the end down did go to the last row.

And I'm assuming you won't mind, but I did take the liberty to change the
name of the macro from blah to kudos.

Many thanks gain,

Steve

assuming the the last of the rows involved can be reliably ascertained
from selecting M3 then pressing End, then the down button (you don't
have to do this, it's just the technique the macro uses to identify the
rows involved) then this code should do it. It assumes the correct sheet
is active:

 

[p45cal]

Good!
On reviewing the code jut now, I notice I left some debugging lines tha
really oughtn't to be there, so please remove these:

Rows(i).Select
cll.Select

they'll slow it down.


That is nothing short of amazing. THANK YOU very much.

 

[Steve]

Thank you again.

Steve

Good!
On reviewing the code jut now, I notice I left some debugging lines that
really oughtn't to be there, so please remove these:

Rows(i).Select
cll.Select

they'll slow it down.



That is nothing short of amazing. THANK YOU very much.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=199126

http://www.thecodecage.com/forumz

.