+ Reply to Thread
Results 1 to 10 of 10

Remove array position

  1. #1
    Registered User
    Join Date
    03-19-2010
    Location
    Portugal
    MS-Off Ver
    Excel 2007
    Posts
    14

    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
    Last edited by Leith Ross; 03-19-2010 at 01:51 PM. Reason: Added Code Tags

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Remove array position

    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.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    03-19-2010
    Location
    Portugal
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Remove array position

    In this code i could use, but the real code is much more complex, and I would prefer not to use dynamic arrays..

    Isnt' there any other way?

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Remove array position

    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.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  5. #5
    Registered User
    Join Date
    03-19-2010
    Location
    Portugal
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Remove array position

    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
    Last edited by dlemos; 03-19-2010 at 03:39 PM.

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Remove array position

    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.

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Remove array position

    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.

  8. #8
    Registered User
    Join Date
    03-19-2010
    Location
    Portugal
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Remove array position

    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
    Last edited by dlemos; 03-20-2010 at 10:35 AM.

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Remove array position

    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.

  10. #10
    Registered User
    Join Date
    03-19-2010
    Location
    Portugal
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Remove array position

    Thank you both very much for all your help.

    Ok, I'll try to avoid using Redim and Redim Preserve. Gonna go read the book now for some tips on Dialog Boxes; I'm hoping that part will be easier =)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1