Does anyone have a duplicate code formula ?

[mercmannick]

does anyone have a duplicates code..


i have a sheet, everyday i have to run thru a column dat sort and delete the duplicate rows,

Also have another daily sheet,

if cell c4=c3 make blank if not dont touch...

Any help would be appreciated

Merc

 

[Greg Truby]

Erik,

The only caveat I might add would be that I normally go ahead and make the background white too. 99.x% of users would have their window background set to white (the default). But there are screwballs like myself who get a notion to alter that from white to some soft pastel. Since I'm already there, setting the conditional formatting, why not just set the background and then I don't have to worry. (Of course, in this context, you'd really need to set the background for ALL the cells to white in this case to keep it neat...)

Merc,

Here's a quick and dirty little something that would actually clear the contents of the cells and not just hide them using CF.

HTH

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ClearDuplicateCells()
<SPAN style="color:#007F00">' Quick little routine to clear cells where value</SPAN>
<SPAN style="color:#007F00">' is the same as the cell immediately above.</SPAN>
    
    <SPAN style="color:#00007F">Dim</SPAN> rngCell <SPAN style="color:#00007F">As</SPAN> Range, rngConstants <SPAN style="color:#00007F">As</SPAN> Range, s <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> lngCell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    <SPAN style="color:#007F00">' If a shape or chart is selected, leave</SPAN>
    <SPAN style="color:#00007F">If</SPAN> TypeName(Selection) <> "Range" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
    <SPAN style="color:#007F00">' If one cell is selected, select current column</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Selection.Count = 1 <SPAN style="color:#00007F">Then</SPAN>
        Selection.CurrentRegion.Columns(Selection.Column - Selection.CurrentRegion.Column + 1).Select
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    
    <SPAN style="color:#007F00">' Don't knock out any formulas</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> rngConstants = Selection.SpecialCells(xlCellTypeConstants, XlSpecialCellsValue.xlNumbers + _
                                                                   xlTextValues + xlErrors + xlLogical)
    <SPAN style="color:#007F00">' This should clear from the bottom to top...</SPAN>
    <SPAN style="color:#00007F">For</SPAN> lngCell = rngConstants.Count <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1
        <SPAN style="color:#00007F">Set</SPAN> rngCell = rngConstants.Cells(lngCell)
        <SPAN style="color:#00007F">With</SPAN> rngCell
            <SPAN style="color:#00007F">If</SPAN> .Row <> 1 <SPAN style="color:#00007F">Then</SPAN>
                <SPAN style="color:#00007F">If</SPAN> .Value = .Offset(-1) <SPAN style="color:#00007F">Then</SPAN> .ClearContents
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> lngCell
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

{Edit}Forgot to add an On Error Resume Next before the call to SpecialCells - just in case there aren't any constants; 'cause that line will error out if it can't find any constants...{/Edit}

 

[mercmannick]

Thanx

Greg Truby

Exactly what i was looking for


Merc

 

[Greg Truby]

Just one more thought on the subject. One of the benefits of the CF solution is that if a user decides to sort the data and does it "without thinking" by sorting on say a date field. Then all of the related info that you deleted is gone and now the presentation looks wrong; and it's quite easy to scramble the data up real good. If you leave all the data in place and just "hide" it with CF, then a sort doesn't result in a mess.

Oh, and you can't really do any Pivot Table stuff if you've gone and deleted data from fields.