Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.
Learn more about Allen...
I can't tell you the number of times I have received raw data from some program or from some person, and the first thing I need to do is remove duplicates from the list. If you find yourself in the same situation, the following macro will be a huge help:
Sub DelDups() Dim rngSrc As Range Dim NumRows As Integer Dim ThisRow As Integer Dim ThatRow As Integer Dim ThisCol As Integer Dim J As Integer, K As Integer Application.ScreenUpdating = False Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address) NumRows = rngSrc.Rows.Count ThisRow = rngSrc.Row ThatRow = ThisRow + NumRows - 1 ThisCol = rngSrc.Column 'Start wiping out duplicates For J = ThisRow To (ThatRow - 1) If Cells(J, ThisCol) > "" Then For K = (J + 1) To ThatRow If Cells(J, ThisCol) = Cells(K, ThisCol) Then Cells(K, ThisCol) = "" End If Next K End If Next J 'Remove cells that are empty For J = ThatRow To ThisRow Step -1 If Cells(J, ThisCol) = "" Then Cells(J, ThisCol).Delete xlShiftUp End If Next J Application.ScreenUpdating = True End Sub
The macro works on a selection you make before calling it. Thus, if you need to remove duplicate cells from the range C15:C59, simply select that range and then run the macro. If you select more than a single column in the range (for instance, C15:E59), then only the first column in the range is affected. When the macro is complete, the duplicate cells are removed, as are any blank cells.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2109) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!