+ Reply to Thread
Results 1 to 12 of 12

Check Range; if equal to X, Y, or Z then keep else delete

  1. 09-01-2009, 02:03 PM #1
    kmjones
    kmjones is offline
    Registered User
    Join Date
    08-10-2007
    Posts
    11

    Check Range; if equal to X, Y, or Z then keep else delete

    I am trying to search a column for specific values.... if the cells in the column equal X, Y, or Z then I want to keep them. Anything else, should be deleted....and not just the cell the entire row that it is in.

    So I'd like it to search column E for a number of different values, if equal keep the data. If not (say E6 is not any of those values) then delete that row (all of row 6)

    Any ideas???
    Last edited by kmjones; 09-02-2009 at 12:35 PM.
    Reply With QuoteRegister To Reply
  2. 09-01-2009, 02:25 PM #2
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Check Range; if equal to X, Y, or Z then keep else delete

    Hello kmjones,

    Do you have a workbook you can post with the some or all of the data?
    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!)
    Reply With QuoteRegister To Reply
  3. 09-01-2009, 03:46 PM #3
    kmjones
    kmjones is offline
    Registered User
    Join Date
    08-10-2007
    Posts
    11

    Re: Check Range; if equal to X, Y, or Z then keep else delete

    Site Data1 Data2 Data3 Total
    100 125 225 1 350
    100 125 225 1 350
    100 125 225 1 350
    100 145 245 2 390
    100 145 245 2 390
    100 145 245 2 390
    100 155 255 3 410
    100 155 255 3 410
    100 155 255 3 410
    100 125 225 4 350
    100 125 225 4 350
    100 125 225 4 350
    100 145 245 5 390
    100 145 245 5 390
    100 145 245 5 390
    100 155 255 6 410
    100 155 255 6 410
    100 155 255 6 410
    100 125 225 7 350
    100 145 245 7 390
    100 155 255 7 410


    So, what I'd like to do is have it search the Data 3 Column. If the value in the cell equals 1, 3, 5, or 7 then Keep the rows ... If not, then delete the row of information associated with that cell.

    Does that make sense?

    So, for this example, because Data3 columns first 3 cells are equal to 1, the information would stay. But the 4th cell in the column would not meet the requirements (since it is 2) and thus, the entire row would be deleted (row 4).

    Let me know if you can think of anything! Thanks for your help!
    Reply With QuoteRegister To Reply
  4. 09-01-2009, 03:50 PM #4
    kmjones
    kmjones is offline
    Registered User
    Join Date
    08-10-2007
    Posts
    11

    Re: Check Range; if equal to X, Y, or Z then keep else delete

    I saw the chart didn't show up very nice... I've attached it in Excel
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 09-01-2009, 05:05 PM #5
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Check Range; if equal to X, Y, or Z then keep else delete

    Hello kmjones,

    This macro will delete any row that doesn't have 1, 3, 5, 7 in column "D" starting at "D2" down to the last entry in "D". You can add other values if you need to to in the Select Case statement in the code. It is marked in red. This macro has been added to the attached workbook.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 09-01-2009, 05:35 PM #6
    kmjones
    kmjones is offline
    Registered User
    Join Date
    08-10-2007
    Posts
    11

    Re: Check Range; if equal to X, Y, or Z then keep else delete

    Great! This is exactly what I was looking for.

    Thank you so much for your help!
    Reply With QuoteRegister To Reply
  7. 09-01-2009, 05:52 PM #7
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Check Range; if equal to X, Y, or Z then keep else delete

    Hello kmjones,

    If your questions have been answered to your satisfaction, please mark this thread as solved.

    How to Mark a thread Solved
    1. Go to the first post #1
    2. Click Edit
    3. Click Go Advanced
    Just below the word Title you will see a drop down with the words "(no prefix)".
    4. Click the drop down and select "[Solved]"
    5. Click Save
    Reply With QuoteRegister To Reply
  8. 09-02-2009, 09:15 AM #8
    kmjones
    kmjones is offline
    Registered User
    Join Date
    08-10-2007
    Posts
    11

    Re: Check Range; if equal to X, Y, or Z then keep else delete

    Hi! One more question, I tried to have this work for another project as well. But, this time the cell "values" are not numbers but initials or alpha-numeric combos.

    Now it doesn't work. Do I need to change the line of Cell.Value to something different?

    Thanks (I will mark the post as solved after this )
    Reply With QuoteRegister To Reply
  9. 09-02-2009, 10:22 AM #9
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Check Range; if equal to X, Y, or Z then keep else delete

    Hello kmjones,

    You have to add the comparative values to the Select Case statement. Initially, it was set to look for only the numbers 1, 3, 5, and 7. What are the new values?
    Reply With QuoteRegister To Reply
  10. 09-02-2009, 10:35 AM #10
    kmjones
    kmjones is offline
    Registered User
    Join Date
    08-10-2007
    Posts
    11

    Re: Check Range; if equal to X, Y, or Z then keep else delete

    Thanks for your continued help....

    The new values can be anything like the following:

    MPPP
    ADRT
    M25T
    Y78
    IO95


    Any suggestions?

    Thanks again!
    Reply With QuoteRegister To Reply
  11. 09-02-2009, 12:24 PM #11
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Check Range; if equal to X, Y, or Z then keep else delete

    Hello kmjones,

    I changed the Select Case statement. It will not delete rows whose column "D" cell contains: MPPP, ADRT, M25T, Y78, IO95
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  12. 09-02-2009, 12:34 PM #12
    kmjones
    kmjones is offline
    Registered User
    Join Date
    08-10-2007
    Posts
    11

    Re: Check Range; if equal to X, Y, or Z then keep else delete

    Aweomse! Perfect.

    Thank you! I will mark the thread as solved.

    Thanks again!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1