hmltnangel
Active Member
- Joined
- Aug 25, 2010
- Messages
- 290
- Office Version
- 365
- Platform
- Windows
Morning all,
I was looking for a way to clear the contents of duplicate cells, retaining the first instance of the data.
However Excel seems to be setup to delete the rows/cells rather than clear the contents. After searching, I found this nice little bit of code for VBA that seems to work. But it takes nearly fifteen minutes to work. Any suggestions for shortening the length of time to allow the code to work, or a better solution?
I was looking for a way to clear the contents of duplicate cells, retaining the first instance of the data.
However Excel seems to be setup to delete the rows/cells rather than clear the contents. After searching, I found this nice little bit of code for VBA that seems to work. But it takes nearly fifteen minutes to work. Any suggestions for shortening the length of time to allow the code to work, or a better solution?
VBA Code:
Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
If WorksheetFunction.CountIf(Columns("A"), Range("A" & i).Value) > 1 Then Range("A" & i).Delete shift:=xlShiftUp
Next i
End Sub