Solved: Delete a specific record from an Excel VBA array?

[paul_0722]

What is the best way to delete a specific record from an Excel VBA array?

Suppose I have an array of five items:

test(1) = red
test(2) = green
test(3) = orange
test(4) = blue
test(5) = yellow

I want to delete test(3), but I find no handy test(3).delete function in Excel VBA - so what is the best way to proceed?

My first thought; copy all but test(3) to another array and ReDim. Seems like a lot of trouble but maybe that's the VBA way?

Any help appreciated

[RonMcK]

Paul,

Here's a little macro that will help you understand a process you can use. In your real-life macro you might want to Re-Dim your array, assuming your process will not be adding any rows to the array after the point of your deletion.

This macro gives you a before and after view of the sample data. So it will make sense, I added a 2nd column for the original row number of each color element. You can re-run the routine without having to restart it.
Option Explicit
Sub DecrementArray()
Again:
Dim test() As String
ReDim test(10, 2)
Dim R As Long
Dim i As Long
Dim j As Long

'Initialize array
test(1, 1) = "red"
test(2, 1) = "green"
test(3, 1) = "orange"
test(4, 1) = "blue"
test(5, 1) = "yellow"
test(1, 2) = 1
test(2, 2) = 2
test(3, 2) = 3
test(4, 2) = 4
test(5, 2) = 5
For i = 1 To UBound(test)
If test(i, 1) = "" Then Exit For
Debug.Print "ArrayPos:" & Str(i), test(i, 1), "OrigPos:" & test(i, 2)
Next
GetElement:
R = InputBox("Enter number of array element to delete", "Decrement Array")
If R < LBound(test) Or R > UBound(test) Then GoTo GetElement

For j = R To UBound(test)
For i = 1 To 2
If test(j, 1) = "" Then Exit For
test(j, i) = test(j + 1, i)
Next
Next
Debug.Print "After deletion"
For i = 1 To UBound(test)
If test(i, 1) = "" Then Exit For
Debug.Print "ArrayPos:" & Str(i), test(i, 1), "OrigPos:" & Str(test(i, 2))
Next
If MsgBox("Do you want to re-run this routine?", vbYesNo, "Again?") = vbYes Then
GoTo Again
End If
End Sub

I hope this answers your questions; post any other questions you may have. :beerchug:

Cheers!

[Bob Phillips]

Ron,

No need to use constant values in code



R = MsgBox("Do you want to return this routine?", vbYesNo, "Again?")
If R = 6 Then Goto Again ' vbYesNo 6=Yes 7=No


becomes



R = MsgBox("Do you want to return this routine?", vbYesNo, "Again?")
If R = vbYes Then Goto Again


or even



If MsgBox("Do you want to return this routine?", vbYesNo, "Again?") = vbYes Then
Goto Again
End If

[RonMcK]

Thanks, Bob. If I hang around here long enough I may learn how to make Excel really sing. The 'or even' version is tres cool.

Thanks, again,

[Bob Phillips]

Thanks, Bob. If I hang around here long enough I may learn how to make Excel really sing. The 'or even' version is tres cool.

Thanks, again,

Yeah, if you are interested in just one path from the MsgBox it saves a variable :giggle

[paul_0722]

Thanks for replies. Seems there really is no simple "delete the third record" type command in VBA, so I'll make use of the code shown.

Thanks again for help - always appreciated!

[RonMcK]

Please mark this thread as solved.

Thanks,