Zwecks Lesbarkeit sind Parts hardcodiert.
Option Explicit
Sub main()
Dim wkbQuelle As Excel.Workbook
Dim wksQuelle As Excel.Worksheet
Dim rng As Excel.Range
Dim lngLastRow As Long
Set wkbQuelle = Application.Workbooks.Open("C:\Test\Mappe1.xlsx")
Set wksQuelle = wkbQuelle.Worksheets("Tabelle1")
With wksQuelle
lngLastRow = IIf(IsEmpty(.Cells(.Rows.Count, "B").Value), _
TruePart:=.Cells(.Rows.Count, "B").End(xlUp).Row, _
Falsepart:=.Rows.Count)
Set rng = .Range("B2:B" & lngLastRow)
End With
MsgBox getAdressA(rng)
wkbQuelle.Close SaveChanges:=False
Set rng = Nothing: Set wksQuelle = Nothing: Set wkbQuelle = Nothing
End Sub
Function getAdressA(ByRef rng As Excel.Range) As Variant
Dim c As Excel.Range
Dim arr() As String
ReDim arr(1 To rng.Rows.Count)
For Each c In rng
arr(c.Row - 1) = c.Value
Next c
getAdressA = Join(arr, ";")
End Function