Basic VBA Examples of Array

Public Sub DecArrayDynamic()

Dim ArrItems() As Variant
Erase ArrItems

Dim lRowA As Long
lRowA = Cells(Rows.Count, 1).End(xlUp).Row
ReDim ArrItems(0 To lRowA)

Dim rcell As Range, rng As Range, i As Double

i = 0
Set rng = Application.ActiveSheet.Range("A1:A" & lRowA)

For Each rcell In rng.Cells
 ArrItems(i) = rcell.Value
 'Debug.Print ArrItems(i)
 i = i + 1
Next rcell

For Each ArrVal In ArrItems
 'IsInArray = (UBound(Filter(YtdReportArr, arrval)) > -1) ' Compare Two Range Arrays
  'If IsInArray = False Then
   Debug.Print ArrVal
  'End If
 Next ArrVal
End Sub

Public Function ReverseRange(rg As Range) As Range()
    Dim arr() As Range, r As Long, c As Long, n As Long
    With rg
        ReDim arr(1 To .Cells.Count) 'resize Range Array
        For r = .Cells(.Rows.Count, 1).Row To .Cells(1, 1).Row Step -1
            For c = .Cells(1, .Columns.Count).Column To .Cells(1, 1).Column Step -1
                n = n + 1
                Set arr(n) = .Worksheet.Cells(r, c) 'set cell in Array
            Next c
        Next r
    End With
    ReverseRange = arr  'return Range Array as function result
End Function

Public Function RangeToArray(rng As Range) As Variant
    Dim i As Long, r As Range
    ReDim arr(1 To rng.Count)

    i = 1
    For Each r In rng
        arr(i) = r.Value
        i = i + 1
     Next r

    RangeToArray = arr
End Function

Public Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Public Function ReverseRange(rg As Range) As Range()
    Dim arr() As Range, r As Long, c As Long, n As Long
    With rg
        ReDim arr(1 To .Cells.Count) 'resize Range Array
        For r = .Cells(.Rows.Count, 1).Row To .Cells(1, 1).Row Step -1
            For c = .Cells(1, .Columns.Count).Column To .Cells(1, 1).Column Step -1
                n = n + 1
                Set arr(n) = .Worksheet.Cells(r, c) 'set cell in Array
            Next c
        Next r
    End With
    ReverseRange = arr  'return Range Array as function result
End Function