deleting data in vba array

R

RobcPettit

Hi, Im working with a dynamic array and have no problem assigning values to an
array, or working with the array in general. What Im having difficulty is
deleting an item in the array then resizing. I know how to add items and resize
using preserve. What Im doing is this. Say my array has 10 values, I use: For I
= 1 to Ubound(myarray) then loop through the array looking to see if a value
matches a certain criteria, if not I do nothing, if so (this bit I cant do) I
want to remove that value from my array (leaving 9 values), resize the array
then use my Ubound. Can this be done. Any advice would be appreciated
Regards Robert
 
B

Bob Phillips

Robert,

If it is the last item that is to be deleted, you can Redim Preserve as with
adding.

If it is not the last item, you will need to shuffle all remaining items
back one and then Redim, sort of like

For i = 10 To UBound(aryNums,1) -1
aryNums(i) =aryNum(i+1)
Next i
Redim Preserve aryNums(UBound(aryNums,1)-1
 
A

Alan Beban

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, something
like the following should work:

n = Application.Match(SoughtValue, arr)
Select Case n
Case 1
arr = SubArray(arr, 2, UBound(arr))
Case UBound(arr)
ReDim Preserve arr(UBound(arr) - 1)
Case Else
arr = MakeArray(SubArray(arr, 1, n - 1), SubArray(arr, n + 1, 10), 1)
End Select

Alan Beban
 
D

Dana DeLouis

If you are willing to work with the values as strings until you are
finished, perhaps another way.. The "Filter" command converts the initial
integers to strings.

Sub Demo()
Dim v
Dim Unique

Unique = Chr(255)
v = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

' Your code to remove the 5th & 9th item
' This example is "Zero" based, so index is 4 & 8
v(4) = Unique
v(8) = Unique

'Now, delete them...
v = Filter(v, Unique, False)
End Sub
 
A

Alan Beban

Interesting. And if the functions are available, the last line can be

v=ConvertBase(Filter(v,Unique,False),1)

for a 1-based array.

Alan Beban
 
H

Harlan Grove

Alan Beban said:
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook,
something like the following should work:

n = Application.Match(SoughtValue, arr)
Click to expand...

Did the OP mention that arr was sorted?
Select Case n
Case 1
arr = SubArray(arr, 2, UBound(arr))
Case UBound(arr)
ReDim Preserve arr(UBound(arr) - 1)
Case Else
arr = MakeArray(SubArray(arr, 1, n - 1), _
SubArray(arr, n + 1, 10), 1)
End Select
Click to expand...
....

Uh, what happens if there's no instances of SoughtValue in arr?

There are times when the direct approach is better.

Dim i As Long, k As Long
For i = LBound(arr) To UBound(arr)
If arr(i) = SoughtValue Then
k = k + 1
ElseIf k > 0 Then
arr(i - k) = arr(i)
End If
Next i
If k > 0 Then ReDim Preserve arr(LBound(arr) To (UBound(arr) - k))

This can remove multiple instances of SoughtValue in arr. And it won't throw
a runtime error if there are no instances of SoughtValue in arr.
 
Top