Useful Hacks, Reviews, Tips and Tricks of anything under the moon and the sun.

How to remove duplicate cells using Excel Macro

No comments
My boss gave me a home assignment yesterday. She forwarded me a workbook that contains user modification request. The workbook contains about 500+ lines. I need to apply new formatting on it and remove all duplicate values. On my previous blog post, I already showed you how to generate a unique list of entries. Today I will share another way to remove duplicate cells using VBA macro.

To create add a macro, follow these steps:
1. On your Excel's Tools Menu, choose Macro then Visual Basic Editor
2. Double-click ThisWorkbook from the Project Explorer on your left side.
3. Copy and paste the following code on right pane.

Sub DelDuplicate()
Dim rngSrc As Range
Dim iRows As Integer
Dim Row As Integer
Dim Row2 As Integer
Dim Col As Integer
Dim J As Integer, K As Integer

Application.ScreenUpdating = False
Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)

iRows = rngSrc.Rows.Count
Row = rngSrc.Row
Row2 = Row + iRows - 1
Col = rngSrc.Column

'Start wiping out duplicates
For J = Row To (Row2 - 1)
If Cells(J, Col) > "" Then
For K = (J + 1) To Row2
If Cells(J, Col) = Cells(K, Col) Then
Cells(K, Col) = ""
End If
Next K
End If
Next J

'Remove cells that are empty
For J = Row2 To Row Step -1
If Cells(J, Col) = "" Then
Cells(J, Col).Delete xlShiftUp
End If
Next J
Application.ScreenUpdating = True
End Sub

4. Now switch to your workbook. Highlight the cells you want to filter.
5. Go back to the VBA editor then click the play button on the toolbar or from the menu click Run > Run Sub.

No comments :

Post a Comment