Remove array position

Hi all

Does anyone know any excel-vba command to remove a specified array position?

For instance:

Please Login or Register  to view this content.

The result would be 1 3 4 5 6 7 8 9 10 instead of 1 2 3 4 5 6 7 8 9 10.
I tried using Redim but it doesn't work since i need to use a static array..

Thank you

Use a dynamic array (there's no reason you can't). Then to delete element i, copy the elements above down one, and Redim Preserve the result.

Hello dlemos,

If you are working with just a 2-D array, you can use the Dictionary object. This combines the features of both an array and collection object. There are 2 arguments that the Dictionary uses: Key and Item.

The Key argument must be unique or you will receive an error message. The Item can be any object, number, date, or string. The only exception is the Item can not be a User Define Type (UDT) structure.

Both the Key and Item are stored in 2 separate arrays named Keys and Items. These are returned by using the .Keys or .Items properties. Each array is 1-D starting with element 0. You can add a reference to your VBA project to the Microsoft Scripting Runtime library or create the object in your code.

The advantage of setting a reference is you have IntelliSense. This will automatically show you the properties, methods and events of the object. This example creates the Dictionary object in the code. ItelliSense is not available to you when you create the Dictionary. You just have to have everything committed to memory.

Please Login or Register  to view this content.

So an Object is like an array?

How do I assign a variable to a certain object position,for instance, with arrays it goes like this:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

?

Can you do these things with objects?How?

thank you for all your help

dlemos,

Leith edited your first post to add code tags. Please take a few minutes to read the forum rules, and then edit your last post to add them.

An Object (in Excel and other languages) is something that typically has properties and methods. In the same way that a variable is a pointer to a location in memory to where the variable is store, an Object is a pointer to a table in memory with entry points for it's methods and properties.

Open the VBE, press the Help button, expand Microsoft Visual Basic Documentation, and below that Visual Basic Language Reference, and below that, Objects. You'll see a list of the objects that are intrinsic to VBA (there aren't many). One of those is the Dictionary object.

If you expand Microsoft Excel Visual Basic Reference, and below that, Objects, up pops an alphabetical list, which is a hint that Excel has hundreds of objects, having, combined, thousands of properties and methods.

IMO, you be better off starting with the basics than diving into the deep end of the pool. Get familiar with basic types of variables and arrays and VBA's flow control structures. Then learn objects as you need them.

Suggest you buy a book, like Walkenbach's VBA for Dummies. I haven't read it, but have heard several recommendations and good reviews.

I've been working with VBA for some time but I have some trouble with particular issues. I got that book you mentioned seems pretty useful.

Anyway, I'm still trying to remove an array position; I got that working with a single array, but now I'm trying to compare to arrays and remove the positions they have in common;

For instance, I have nome=[200,100,0] and nome2=[300,200,100,0] (they're both dynamic arrays but I've filled them with these values).

I want that after the code, nome2=[300], because 300 is the only value that isn't shared by both arrays.

I get "subscript out of range error"

Please Login or Register  to view this content.

One more thing. if I remove the following lines:

Please Login or Register  to view this content.

I don't get the error and as an output I get 300 0 0 0...but I would like to get just 300

Please Login or Register  to view this content.

I applied a variable naming convention (mine), and eliminated module-level variables that should be procedure-level.

I took the Redims out of the loops. It's an expensive operation, and shouldn't be done in a loop unless necessary (which is a good case for using a Dictionary as Leith suggested, when you're ready). Redim Preserve is more expensive, and shouldn't be done at all if it's not necessary. I eliminated Preserve in those cases.

The default base for array dimensions in VBA is 0, so you should get in the habit of specifying them explicitly (whether 0 or not, IMO).

I'm fuzzy about what you're trying to do in Remove, at least insofar as this example.