Now, if you use the formula I provided earlier, you can then use this code to
move all remaining visible entries in column C up to begin in row 2 without
any intervening empty cells, and they will be converted to hard values rather
than as the result of the formulas. Makes cutting and moving them elsewhere
easier. If you don't want that done, there's just one line of code in the
section that you have to remove to prevent it:


Sub RemoveEmptyCells()
'assumes you are on the sheet
'with empty cells in column C
'to be removed
'Assumes at least one entry
'in column C somewhere!
Application.ScreenUpdating = False
Range(Range("C65535").End(xlUp).Address).Select
Do While ActiveCell.Row 1
'carve in stone so you can copy easily
ActiveCell.Formula = ActiveCell.Value
If IsEmpty(ActiveCell) Or ActiveCell.Value = "" Then
'delete cells with no visible content
Selection.Delete Shift:=xlUp ' remains on same row
End If
ActiveCell.Offset(-1, 0).Activate
Loop
Application.ScreenUpdating = False

End Sub


"JLatham" wrote:

in column C use this formula (based on your example, adjust cell ranges to
match reality)

=IF(COUNTIF(A$1:A$5,B1)0,B1,"")

extend that down to match entries in column B. Duplicates will be marked by
echoing the contents of column B in Column C. That doesn't deal with the
need to delete anything - just shows you which entries appear in both lists.

"username123" wrote:


anybody? I thought this might have been simple.


--
username123
------------------------------------------------------------------------
username123's Profile: http://www.excelforum.com/member.php...o&userid=35863
View this thread: http://www.excelforum.com/showthread...hreadid=556564