Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata
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
  • No labels