Delete item from array

sektor · April 4th, 2008, 12:19 AM

Code in Excel 2007

I have an array with names of towns, but some names are duplicated. What is the best method of removin' these duplicates? Don't suggest me to write all items to some sheet, then to use RemoveDuplicates property and then assign filtered names back to array!

Dim vaTowns As Variant

With Sheets("data")
vaTowns = .Range(.Cells(rngRow.Row, "G"), _
.Cells(rngRow.Row, "G").End(xlDown))
End With

tstav · April 4th, 2008, 03:26 PM

There are several ways to do this. Here's one:

Since your vaTowns accepts values from a Sheet, it is a two-dimension, 1-based array.
1. Create one more 2-dimension, 1-based dynamic array.
2. Parse the original array's items one by one and check them against the values of the second array. Add each item that does not match, to the second array. You are done.
3. If you want, you can transfer the new array's items into your sheet (I'm showing the way with an example)

Take note: In a multi-dimension dynamic array you can only change the highest dimension (watch this happen in the code).

Code:

Sub RemoveArrayDuplicates()
Dim vaTowns As Variant, vaTownsDistinct() As Variant
Dim lngI As Long, lngK As Long
With Sheets("data")
   vaTowns = .Range(.Cells(rngRow.Row, "G"), .Cells(rngRow.Row, "G").End(xlDown))
End With
ReDim vaTownsDistinct(1 To 1, 1 To 1)
vaTownsDistinct(1, 1) = vaTowns(1, 1)
For lngI = LBound(vaTowns, 1) + 1 To UBound(vaTowns, 1)
   For lngK = 1 To UBound(vaTownsDistinct, 2)
      If vaTowns(lngI, 1) = vaTownsDistinct(1, lngK) Then Exit For
   Next 'lngK
   If lngK > UBound(vaTownsDistinct, 2) Then 'didn't find it
      ReDim Preserve vaTownsDistinct(1 To 1, 1 To UBound(vaTownsDistinct, 2) + 1)
      vaTownsDistinct(1, UBound(vaTownsDistinct, 2)) = vaTowns(lngI, 1)
   End If
Next
Range("E5").Resize(UBound(vaTownsDistinct, 2)).Value = WorksheetFunction.Transpose(vaTownsDistinct)
End Sub

tstav · April 4th, 2008, 04:09 PM

And this is another way, rather simpler since it is using a 1-dimension array.

Code:

Sub RemoveArrayDuplicates2()
Dim vaTowns As Variant, vaTownsDistinct As Variant
Dim lngI As Long, lngK As Long
On Error Resume Next
With Sheets("data")
   vaTowns = .Range(.Cells(rngRow.Row, "G"), .Cells(rngRow.Row, "G").End(xlDown))
End With
ReDim vaTownsDistinct(1 To 1)
vaTownsDistinct(1) = vaTowns(1, 1)
For lngI = LBound(vaTowns, 1) + 1 To UBound(vaTowns, 1)
   If WorksheetFunction.Match(vaTowns(lngI, 1), vaTownsDistinct, 0) Then
      If Err Then 'no match
         Err.Clear
         ReDim Preserve vaTownsDistinct(1 To UBound(vaTownsDistinct) + 1)
         vaTownsDistinct(UBound(vaTownsDistinct)) = vaTowns(lngI, 1)
      End If
   End If
Next
On Error GoTo 0
For lngK = 1 To UBound(vaTownsDistinct)
   Range("E4").Offset(lngK, 0).Value = vaTownsDistinct(lngK)
Next
End Sub

tstav · April 7th, 2008, 01:42 AM

Any response? Any comments? Sektor, are you there?
Did I just waste my time? ................................

sektor · April 8th, 2008, 02:54 AM

tstav, sorry for no response - just was busy. I wanna thank you much! All work fine! You're cool, man :) And why are "Starting Member" while you're wise man??? And this would better to write down your ICQ number :)

tstav · April 8th, 2008, 03:45 AM

I'm happy it worked for you, sektor.

Quote:

quote:And why are "Starting Member"

I joined the forum only a few weeks ago, that's why. I like Wrox books so I thought why not join the forum, too. I love what I do (programming) and I would give 'titles' and 'ranks' very little notice. It's what's in the code that matters, not who writes it.

No ICQ number. I'm quite busy as it is.

Nice talking to you, sektor. I tend to expect some kind of acknowledgement from people who ask for something and get a response, that's why I wondered if you were there.

sektor · April 8th, 2008, 06:20 AM

By the way, you made mistake in this part of your code:

For lngK = 1 To UBound(vaTownsDistinct)
Range("E4").Offset(lngK, 0).Value = vaTownsDistinct(lngK)
Next

The lngK variable should be after Next.
It seems you mixed up with Visual Basic .NET syntax :)

tstav · April 8th, 2008, 06:48 AM

Adding the variable's name after the 'Next' clause is absolutely optional.
You want to add it? No problem. You want to skip it? Fine.
No mix up in this.
If it needed to be there and was missing, the code wouldn't compile (wouldn't run) and you would have seen an error message pop up. I believe you saw no such thing.

sektor · April 8th, 2008, 07:30 AM

Oh, man! Don't abuse, 'cause you're right! The variable is REALLY optional after Next - I just tried it out myself. My apologizes... But I got into habit to define this variable, that's why I was confused with your code. Apologizes once again...

tstav · April 8th, 2008, 07:44 AM

No offense taken sektor.

It just felt strange to see you be so definitive over catching this 'mistake' when you had already said 'All work fine!'