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
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
' 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 139
Downloads 49
CodeID
DB ID