I need a VBA array filter
- or -
Post a project like this- Posted:
- Proposals: 12
- Remote
- #1328349
- Expired
Description
The problem I am running into is that my array ends up being extremely long, with lots of blanks (because it populates with blank instead of name if the client doesn't fit criteria) and also, it can populate the client's name multiple times.
So basically, I want the final array to be a list of clients names that fit the crop & year criteria, but with all the duplicate names and blank spaces removed. With that final array, I delete the old list of names on my report page and insert those names to be used in other ways.
Blaine N.
0% (0)New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
Hi Blaine, Is this still on? Thanks, Tom
-
Hi Blaine,
if my understanding is correct you need a routine that will remove the duplicates and blank entries from your final array, right?
LucyBlaine N.19 Oct 2016That is correct. The array is basically populated based on a loop and conditional statement that says:
For x = 1 to NumberOfClients
If Crop = A And Year = B Then
Array(x) = Client Name
Else
Next
End If
But this obviously turns the array into a long list with lots of blanks and lots of duplicates, which I want to remove.Lucy S.19 Oct 2016Ok, here is a function which filters duplicates and empty strings:
Sub unik()
Dim d As Object
Set d = CreateObject("Scripting.Dictionary")
'Set d = New Scripting.Dictionary
Dim aFirstArray() As Variant
aFirstArray() = Array("car", "street", "bike", "house", "table", _
"chair", "car", "window", "street", " ", " ")
Dim i As Long
For i = LBound(aFirstArray) To UBound(aFirstArray)
If Not IsEmpty(aFirstArray(i)) And (Not Trim(aFirstArray(i) & vbNullString) = vbNullString) Then
d(aFirstArray(i)) = 1
End If
Next i
Dim v As Variant
For Each v In d.Keys()
'd.Keys() is a Variant array of the unique values in myArray.
MsgBox (v)
Next v
End Sub