CleanArray

Cleans array of unwanted items
This usually needed after you merge two arrays, if arrays are from Excel sheet ranges, they most likely have blank cells, N/A's , etc ...
This was part of larger project to rank numbers in VBA

CodeFunctionName
What is this?

Public

Tested

Original Work
Function CleanArray(Array2Clean, Optional CleanFrom = 12)
    ' Clean array of unwanted items
    ' 12 = blank, N/A, non-numeric
    ' 1 = blank
    ' 2 = N/A
    ' 3 = ....
    '
    Dim returnThis() As Variant
    counter = 1
    For i = 1 To UBound(Array2Clean)
        AddMe = 1
        If CleanFrom = 1 And Array2Clean(i) = "" Then AddMe = 0
        If CleanFrom = 2 And UCase(Array2Clean(i)) = "N/A" Then AddMe = 0
        If CleanFrom = 12 And Array2Clean(i) = "" Then AddMe = 0
        If CleanFrom = 12 And UCase(Array2Clean(i)) = "N/A" Then AddMe = 0
        If CleanFrom = 12 And Not IsNumeric(Array2Clean(i)) Then AddMe = 0
       
        If AddMe = 1 Then
            ReDim Preserve returnThis(1 To counter)
            returnThis(counter) = Array2Clean(i)
            counter = counter + 1
        End If
    Next
    CleanArray = returnThis
End Function

Array2Clean, Optional CleanFrom = 12

Views 138

Downloads 49

CodeID
DB ID

ANmarAmdeen
602
Attachments
Revisions

v1.0

Wednesday
September
22
2021