Sub example_1() 'двумерные массивы (на выбор)
Dim x
x = Range("A1", Cells(Rows.Count, 1).End(xlUp)).Value
x = Range("A1", Cells(5, Columns.Count).End(xlToLeft)).Value
x = Range("A1").CurrentRegion.Value
With Sheets("Sheet1")
 x = .Range("A1:D" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
 x = .UsedRange.Value
End With
End Sub

Sub example_2() 'одномерный массив (на выбор), индексация всегда с 1
Dim x
With Range("A1", Cells(Rows.Count, 1).End(xlUp)) 'из столбца
 x = WorksheetFunction.Transpose(.Value)
End With
With WorksheetFunction 'из строки
 x = .Transpose(.Transpose(Range("A1", Cells(1, Columns.Count).End(xlToLeft)).Value))
End With
x = Application.Index(Range("A1", Cells(1, Columns.Count).End(xlToLeft)).Value, 1, 0)
End Sub

Sub example_3() 'одномерный массив без дубликатов из столбца с заголовком
Dim x, i&
i = ActiveSheet.UsedRange.Columns(1).Rows.Count
x = Filter(Evaluate("TRANSPOSE(IF(COUNTIF(OFFSET(a2:a" & i & ",0,0,ROW(1:" & i - 1 & _
 ")),a2:a" & i & ")=1,a2:a" & i & ",CHAR(126)))"), "~", 0)
Range("B1").Resize(UBound(x) + 1).Value = WorksheetFunction.Transpose(x)
End Sub

Sub example_4() 'одномерный массив без дубликатов из столбца без заголовка
Dim x
With ActiveSheet.Cells(1).CurrentRegion.Columns(1)
 x = Filter(.Parent.Evaluate("TRANSPOSE(IF(COUNTIF(OFFSET(" & .Address & ",0,0,ROW(1:" & .Rows.Count & _
 "))," & .Address & ")=1," & .Address & ",CHAR(126)))"), "~", 0)
End With
Range("B1").Resize(UBound(x) + 1).Value = WorksheetFunction.Transpose(x)
End Sub

Sub example_5() 'одномерный массив без пустых значений из столбца
Dim x
With Range("A1", Cells(Rows.Count, 1).End(xlUp))
 x = Split(Replace(Join(Filter(Split("~" & Join(Application.Transpose(.Value), "~|~") & "~", "|"), _
 "~~", False), "|"), "~", ""), "|")
End With
Range("B1").Resize(UBound(x) + 1).Value = WorksheetFunction.Transpose(x)
End Sub

Sub example_6() 'массив из строки (только для англ. алфавита!)
Dim myStr As String, x: myStr = "AsDfghErt"
x = Split(StrConv(myStr, 64), Chr(0))
Range("B1").Resize(UBound(x)).Value = WorksheetFunction.Transpose(x)
End Sub