non duplicate values
 

Hi Everyone,
I have a following formula in D1
"=IF(COUNTIF($A$1:$B$20,A1)=1,A1,"")"
but is there a way when the A1<1 look for next row. my unique list
aray is d1-d20. so far i tried following but I am very confused and the
code is not working of course:) And I have been posting almost similar
questions log time but no one replies. I`m assuming before I was not
very clear. If the question is not clear kindly reply me to clarify....
Dim x, n
Dim ncell
For n = 1 To 19
ncell = Sheet2.Range("b" & n)
x = Application.WorksheetFunction.CountIf(Sheet1.Range ("a1:b10"),
ncell)
If x = 1 Then
Sheet2.Range("d" & n) = Sheet2.Range("b" & n)
n = n + 1
End If
Next
can anyone help me?

non duplicate values
 

Sorry I did not carefully check the forum. Actually there is a similar
question posted previously. It was replied by Mr. Tom which is
perfectly working for me. So my question's answer is as follows.
Thanks a lot.


Dim rng As Range, rw As Long
Dim rngA As Range, cell As Range
Set rngA = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
Set rng = Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp))
rw = 1
For Each cell In rng
If Application.CountIf(rngA, cell.Value) = 0 Then
Cells(rw, "E").Value = cell.Value
rw = rw + 1
End If
Next
_________________________________________
wrote:
Hi Everyone,
I have a following formula in D1
"=IF(COUNTIF($A$1:$B$20,A1)=1,A1,"")"
but is there a way when the A1<1 look for next row. my unique list
aray is d1-d20. so far i tried following but I am very confused and the
code is not working of course:) And I have been posting almost similar
questions log time but no one replies. I`m assuming before I was not
very clear. If the question is not clear kindly reply me to clarify....
Dim x, n
Dim ncell
For n = 1 To 19
ncell = Sheet2.Range("b" & n)
x = Application.WorksheetFunction.CountIf(Sheet1.Range ("a1:b10"),
ncell)
If x = 1 Then
Sheet2.Range("d" & n) = Sheet2.Range("b" & n)
n = n + 1
End If
Next
can anyone help me?