Compare Lists
 

I could use some help with validating items in a list with VBA. What
I would like to do is compare List A and List B. If an item is in
List A but not in List B I would like to delete item from List A.

Thanks for any help available.

Regards,

David

Compare Lists
 

I haven't got the code to hand but you can achieve your
requirement by using the Filter function .. see the VBA
help files for documentation and example. Filter also
copes with case sensitivity.

-----Original Message-----
I could use some help with validating items in a list
with VBA. What
I would like to do is compare List A and List B. If an
item is in
List A but not in List B I would like to delete item from
List A.

Thanks for any help available.

Regards,

David
.

Compare Lists
 

use the match function


eg

sub quicktest()
for rw = firstrow to lastrow' of 'A'
item = cells(rw,cl) ' cl is the column letter of A
if notmatched(item) then
cells(rw,cl=""
end if
next
end sub
function notmatched(item as string) as boolean
on error resume next
dim res
res = Application.WorksheetFunction.Match(item,range
("b1:b100")
if err.number= 0 then
' matched
notmatched = false
else
err.clear
notmatched = true
end if
end function


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
I could use some help with validating items in a list
with VBA. What
I would like to do is compare List A and List B. If an
item is in
List A but not in List B I would like to delete item
from List A.

Thanks for any help available.

Regards,

David
.

Compare Lists
 

Here's some sample code:

Sub VerifyA()
a = Split("Jan,Feb,Mar,Apr", ",")
b = Split("Jan,Mar,Apr,May", ",")
c = a
For i = LBound(a) To UBound(a)
If -1 = UBound(Filter(b, a(i), True,
vbTextCompare)) Then
c = Filter(a, a(i), False, vbTextCompare)
End If
Next
a = c
MsgBox Join(a) ' will keep Jan,Mar,Apr in a
End Sub


-----Original Message-----
I haven't got the code to hand but you can achieve your
requirement by using the Filter function .. see the VBA
help files for documentation and example. Filter also
copes with case sensitivity.

-----Original Message-----
I could use some help with validating items in a list
with VBA. What
I would like to do is compare List A and List B. If an
item is in
List A but not in List B I would like to delete item
from
List A.

Thanks for any help available.

Regards,

David
.

.