dictionaryオブジェクトを連想配列的に使って、雑多なリストから重複の無いリストを生成します。

Sheets("シート１").range("A1").value = "りんご"

Sheets("シート１").range("A2").value = "みかん"

Sheets("シート１").range("A3").value = "もも"

Sheets("シート１").range("A4").value = "すもも"

Sheets("シート１").range("A5").value = "みかん"

Sheets("シート１").range("A6").value = "りんご"

のデータがある時に、次の標準モジュールを定義し、”Sub hoge”を実行すると、

----------標準モジュールここから----------

Sub hoge()

Dim name_no_overlap_list As Object

Call get_no_overlap_list(name_no_overlap_list, "シート１", 1, 1)

Call output_no_overlap_list(name_no_overlap_list, "シート２", 1, 1)

End Sub

Sub get_no_overlap_list(ByRef Dic As Object, ByVal get_sheet_name As String, _

ByVal get_row_no As Long, ByVal get_column_no As Long)

    Dim i As Long

    Dim buf As String

    Dim Keys As Variant

    i = get_row_no

    Set Dic = CreateObject("Scripting.Dictionary")

    While Sheets(get_sheet_name).Cells(i, get_column_no).Value <> ""

        buf = Sheets(get_sheet_name).Cells(i, get_column_no).Value

        If Not Dic.Exists(buf) Then

            Dic.Add buf, buf

        End If

    i = i + 1

    Wend

End Sub

Sub output_no_overlap_list(ByVal Dic As Object, ByVal out_sheet_name As String, _

ByVal out_row_no As Long, ByVal out_column_no As Long)

    Dim i As Long

    Dim buf As String

    Dim Keys As Variant

    Keys = Dic.Keys

    For i = 0 To Dic.Count - 1

        Sheets(out_sheet_name).Cells(i + out_row_no, out_column_no) = Keys(i)

    Next i

End Sub

----------標準モジュールここまで----------

重複の無いリストである、

Sheets("シート２").range("A1").value = "りんご"

Sheets("シート２").range("A2").value = "みかん"

Sheets("シート２").range("A3").value = "もも"

Sheets("シート２").range("A4").value = "すもも"

の出力が得られます。

リスト自体は、

Call get_no_overlap_list(name_no_overlap_list, "シート１", 1, 1)

の時点で参照渡ししている、name_no_overlap_listに作成されています。

Call output_no_overlap_list(name_no_overlap_list, "シート２", 1, 1)

では出力を行っています。