Highlight Duplicates within separate groups of data

[Dave T]

Hello All,

I have just looked through most of the postings I could find on the board regarding VBA to identify duplicates. Virtually all of these use the entire column when checking for duplicates.

I am after code which will identify duplicates in column A within a contiguous range of data.
For example there is a heading in column B and below this photo details will be entered and in the next column the photo number is entered.
Finally a number (for sorting) is entered in column A. The photos will be numbered 1 to whatever. So I need code which will highlight duplicates in that contiguous range of data.
Then there is one or two blank rows before another batch of photo details are entered. This means the same sorting numbers are used and are separated by a blank row or rows.

Can someone help me with some code which will make the fill red and when the duplicated has been corrected the fill colour is removed.

Thanking you in advance.

Regards,
Dave T

DUPLICATES - Many Macros.xls
	A	B	C	D
29		PN3215
30	1	SOUTHERNAPPROACH	9107
31	3	NORTHERNAPPROACH	9105
32	2	GENERALUNDERSIDEOFWESTERNSTONEARCH	9111
33	1	WESTERNELEVATION	9104
34	3	GENERALUNDERSIDE	9110
35
36		PN7583
37	1	SOUTHERNAPPROACH	9111
38	2	NORTHERNAPPROACH	9112
39	1	GENERALUNDERSIDE	9113
Sheet 9

 

[Peter_SSs]

I have noticed that there is a problem when you run your border macro which puts a border around all data in columns A to C, not including the PN** heading. If you do this and move onto another range of data, the data validation kicks in and prevents duplicates being entered.

I thought that was a requirement ...

The prevention of duplicates VBA provided by Peter works perfectly, but I am wondering if it is possible to add another two conditions to the existing criteria…

Extra Data Validation criteria:
1) Only allow ‘Whole numbers’
2) Numbers within each separate group of data must be within sequential order

Perhaps I haven't understood what you are trying to say. Could you post a screen shot of a small sample sheet just before the problem occurs, and also say what value you then tried to enter in what cell?

 

[Dave T]

Hello Peter,

Wouldn't you know it... tried to recreate the problem to post and everything works fine.

Just out of curiosity I was going to use an HTML Maker to post a screen shot like you suggested but thought this would not give the whole picture or be able show what was happening. So I took some sequential screen shots and pasted them in a Word document along with describing what I was doing. Can Word documents or PDF’s been posted and if not could these be converted to another format to enable posting ???

Your border macro was just another approach to achieving the same end result. Your macro applies borders to the entire worksheet in one go (using column C to apply borders A to C).

When I use the worksheet I will enter various ranges of data in columns B and C and later add the sorting numbers in column A, then run to sort macro for that specific range then move to the next range of data and add the sorting numbers to that, sort and so on…

My thinking was that the border macro would only be applied to the specific range of data being sorted not all of the ranges in the worksheet. But as I said before, I had not considered your solution.
I was just trying to play around with the Sort_A_to_C() With ActiveCell.CurrentRegion code, which sorts a specific range to create a macro that would apply borders to that range only based on data in column C.

Another question…
What would need to be added to the code to remove the borders if I made mistake and re-entered data before running to border macro again.

Thanks again and sorry for any confusion.

Regards,
Dave T

 

[Dave T]

Hello All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I modified the Sort_A_to_C macro to now be:
<o></o>

Option Explicit<o:p></o:p>
<o:p></o:p>
Sub Borders_A_to_C()<o:p></o:p>
   With ActiveCell.CurrentRegion<o:p></o:p>
       If .Column <= 4 And .Rows.Count > 1 Then<o:p></o:p>
     .BorderAround LineStyle:=xlContinuous, Weight:=xlThin, ColorIndex:=0<o:p></o:p>
     With .Borders(xlInsideVertical)<o:p></o:p>
       .LineStyle = xlContinuous<o:p></o:p>
       .ColorIndex = 0<o:p></o:p>
       .Weight = xlThin<o:p></o:p>
     End With<o:p></o:p>
     With .Borders(xlInsideHorizontal)<o:p></o:p>
       .LineStyle = xlContinuous<o:p></o:p>
       .ColorIndex = 0<o:p></o:p>
       .Weight = xlThin<o:p></o:p>
     End With<o:p></o:p>
       End If<o:p></o:p>
   End With<o:p></o:p>
End Sub

<o></o>
<o></o>
Just need to work out how to not have borders around the header row of each range i.e. the cell in column B with “PNxxx” (nothing in cells either side).

This may not be the best option and Peter's suggestion may be easier but thought I would see if this worked.

Once again I just need to add code to remove any existing borders if a row in columns A, B & C is blank.

Regards,
Dave T

 

[Dave T]

Hello Peter or whoever can answer this question,

Within the last part Peter's reply #14 from March 1st 2010 he created some VBA that put the prefix "IMG_” before the photo number and added “.JPG” after the number.

Set ChangedD = Intersect(Changed, Columns("D"))
If Not ChangedD Is Nothing Then
    For Each c In ChangedD
        c.Offset(, -1).Value = "IMG_" & c.Value & ".JPG"
    Next c
End If

This works fine when I enter a number, say 1234, in a cell in column D (e.g. in adjacent cell in column C the result will be IMG_1234.JPG), but when I delete the number in column D the number part is deleted in column C but the IMG_.JPG part is retained.

What do I need to add to the code to clear all of the contents of the adjacent cell in column C if I clear the contents of a cell in column D ???

Thanks again for any replies,

Regards
Dave T

 

[Peter_SSs]

Replace that section with

<font face=Courier New><SPAN style="color:#00007F">Set</SPAN> ChangedD = Intersect(Changed, Columns("D"))<br><SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> ChangedD <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> ChangedD<br>        <SPAN style="color:#00007F">If</SPAN> c.Value = "" <SPAN style="color:#00007F">Then</SPAN><br>            c.Offset(, -1).ClearContents<br>        <SPAN style="color:#00007F">Else</SPAN><br>            c.Offset(, -1).Value = "IMG_" & c.Value & ".JPG"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN></FONT>

 

[Dave T]

Hello Peter,

Once again you have delivered the goods.
Thank you very much.

Looking at you solution I was very close in my attempt to modify your original code.

Regards,
Dave T