Monday, May 11, 2009

find dup()

Sub FindDups()'' NOTE: You must select the first cell in the column and' make sure that the column is sorted before running this macro'ScreenUpdating = FalseFirstItem = ActiveCell.ValueSecondItem = ActiveCell.Offset(1, 0).ValueOffsetcount = 1Do While ActiveCell <> ""If FirstItem = SecondItem ThenActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0, 0)Offsetcount = Offsetcount + 1SecondItem = ActiveCell.Offset(Offsetcount, 0).ValueElseActiveCell.Offset(Offsetcount, 0).SelectFirstItem = ActiveCell.ValueSecondItem = ActiveCell.Offset(1, 0).ValueOffsetcount = 1End IfLoopScreenUpdating = TrueEnd Sub
/* remove trailing and leading white spaces */
Sub WhiteSpacer()Dim cel As Range, rg As RangeSet rg = SelectionIf rg.Cells.Count = 1 Then Set rg = ActiveSheet.UsedRangeSet rg = rg.SpecialCells(xlCellTypeConstants)If rg Is Nothing Then Exit SubApplication.ScreenUpdating = TrueFor Each cel In rg.Cells 'Replace requires Excel 2000 or later. For Excel 97, use Application.Substitute instead cel = Trim(Replace(cel, Chr(160), Chr(32))) 'VBA Trim removes leading and trailing spaces only 'cel = Application.Trim(Replace(cel, Chr(160), Chr(32))) 'TRIM function removes leading and trailing spaces, and converts multiple spaces in succession into a single oneNextApplication.ScreenUpdating = TrueEnd Sub