Select unique values and the last one from them

[rguy84]

Say you have a list of names A1:Axxx, there are a few duplicates. You will be using that list to populate a listbox. B1:Bxxx holds a value (doesnt quite matter here), the last value is selected. So if A1,A4, A7 says Mary, A7,b7's Mary is used vs A1 or A4.

How do I do this in VBA? So far:

    With lbox
        .ColumnCount = 2
        .RowSource = "A1:B" & Sheets("Contact").Range("B" & Rows.Count).End(xlUp).Row
    End With

 

[venkat1926]

try this macro

Sub test()
Dim last As Range, cfind As Range
Set last = Range("A1").End(xlDown)
Set cfind = Cells.Find(what:="Mary", lookat:=xlWhole)
Set cfind = Cells.FindPrevious(after:=last)
MsgBox cfind.Address
End Sub

 

[rguy84]

Venkat, The problem with that is the what:="____" would actually be a variable.

 

[venkat1926]

will this not work

Sub test() Dim last As Range, cfind As Range,nname as string
nname="Mary"
 Set last = Range("A1").End(xlDown)
 Set cfind = Cells.Find(what:=nname, lookat:=xlWhole)
 Set cfind = Cells.FindPrevious(after:=last) M
sgBox cfind.Address
 End Sub

 

[rguy84]

But wouldn't that only show me "Mary"?

 

[venkat1926]

yes nname is a variable. you enter any other name in this code

nname=" "

if you want you change this stament as

name=inputbox("type the name you want e.g. Mary")