deleting data in vba array
 

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

deleting data in vba array
 

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

--
HTH

-------

Bob Phillips
"RobcPettit" wrote in message
...
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

deleting data in vba array
 

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

RobcPettit wrote:

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

deleting data in vba array
 

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


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"RobcPettit" wrote in message
...
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

deleting data in vba array
 

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

Dana DeLouis wrote:

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

deleting data in vba array
 

"Alan Beban" wrote...
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)

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
....

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.

deleting data in vba array
 

Thankyou to all of you for your replys, this gives me plenty to work
with, Regards Robert


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!