How to write a macro to delete 2 or more same value cell and keep the latest cell only, eg, cell a1=xx, a2=xx, a3=xx, a4=xx, I would like to delete a1, a2, a3 and keep a4 only without using cell reference.

What you are asking is very possible, and probably very easy. However - the question in its current form can't be easily answered. Do you want the cells cleared or removed? Are they part of a list, so you want the list to be re-arranged? Is the latest cell always at the bottom of the pile? How often would you like the macro to run, and what would you like to trigger it? Are the elements calculated fields or hard-coded? If they are calculated, then are other parts of the list dependent on them (ie. are we going to screw something else up if we delete them)? Will the list always be in the same place? Do you want just the one cell deleted or is it part of a row of information that needs to go? There are almost certainly more questions than this that need to be asked. Writing a quick macro involves knowing exactly what you want, or you end up taking much more time further down the line. Cheers Tom

Just delete any repeated cell value, that is all.

Well, this would work, then. (I've written it in a way that doesn't require using greater than or less than symbols which this forum doesn't display correctly. That way you can just copy and paste the code directly into the XL VBA module you want) Option Explicit Sub CleanList() Dim t As Integer, c As Integer, x As Integer t = Cells(65535, 1).End(xlUp).Row Do For x = 1 To t - 1 If x = t Then Exit For If Cells(x, 1) = Cells(t, 1) Then If x Mod 2 = 0 Then Cells(x, 1).Delete (xlShiftToLeft) Else Cells(x, 1).Delete (xlShiftUp): x = x - 1: t = t - 1 Next x t = t - 1 If t = 1 Then Exit Do Loop End Sub Sorry its a bit rough and ready. Cheers Tom

Thanks Tom