+ Reply to Thread
Results 1 to 5 of 5

Delete cells not in array

  1. #1
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    548

    Delete cells not in array

    This macro looks at cell values and compares them to an array.
    If the cells in column C don't match one of the selected array values (cell B2), then the row is supposed to be deleted.
    For some reason, only half the relevant cells in column C are deleted.

    Capture.PNG
    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Delete cells not in array

    Hi

    try this. The reason it skips rows is that when you delete row 9 for example, row 10 then becomes row 9 but the counter is up to row 10 so row 10 (which is now row 9) gets skipped. I rearranged some of your code so it starts at the bottom and works backwards so you avoid the row skipping

    Please Login or Register  to view this content.
    Happy with my advice? Click on the * reputation button below

  3. #3
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    548

    Re: Delete cells not in array

    Thanks for that, it works as intended.

  4. #4
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Delete cells not in array

    Hi there,

    You don't need a function to tell you if an item exists in an array and you can delete rows from top to bottom you just need to create a range first and then delete rows from it (if any) at the end i.e.

    Please Login or Register  to view this content.
    Not sure how many rows you have but you can do what you're after more simply like this:

    Please Login or Register  to view this content.
    Regards,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  5. #5
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    548

    Re: Delete cells not in array

    Thanks, these work as well.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA to Grab Right 4 Characters of Array of Cells and Paste to Same Array
    By ktennies in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-05-2016, 07:44 PM
  2. [SOLVED] Help with: Range Array SelectCase Loop delete: Array All Sheet.Names
    By dlow in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-08-2015, 07:11 PM
  3. Typing an array into a formula rather than referring to an array of cells
    By Cookstein2 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-03-2013, 12:59 PM
  4. delete row array
    By ilkamalo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-15-2010, 09:35 AM
  5. Extracting cells from one array based on cells in another array
    By JPKenny in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-02-2010, 03:08 AM
  6. Replies: 0
    Last Post: 09-24-2008, 06:33 PM
  7. [SOLVED] delete an array
    By anton in forum Excel General
    Replies: 1
    Last Post: 10-18-2005, 10:05 AM

Tags for this Thread

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