+ Reply to Thread
Results 1 to 11 of 11

Delete values in one column based on values in a second column

  1. #1
    Registered User
    Join Date
    10-13-2022
    Location
    Northamptonshire, England
    MS-Off Ver
    Excel 2016
    Posts
    56

    Delete values in one column based on values in a second column

    I have a macro-enabled workbook in which I want to delete the vales from cells in one column based on values in another column. This example should make my meaning clearer:
    DeleteValues.png
    I want to delete values in the left column if the values in the right column are > 20 or < 0 so that, in this case, the last value in the left column would be 12.40 The data is such that the values in the two columns are always in ascending order.

    One possible approach (which I have not been able to implement) might be to find the row number (N) of the first cell in the second column which meets the criteria (> 20 or <0) and then set the range to be deleted to be "A" & N & ":A" - assuming the left column is "A"

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,660

    Re: Delete values in one column based on values in a second column

    How large of a range do you have?
    Looping would be the slowest approach but with today's computers, a not too large of a range would be OK.
    Assuming a header in columns A and B
    Please Login or Register  to view this content.
    If you really want to go the way you suggested, let us know.
    The inherent weakness of the liberal society: a too rosy view of humanity.

  3. #3
    Registered User
    Join Date
    10-13-2022
    Location
    Northamptonshire, England
    MS-Off Ver
    Excel 2016
    Posts
    56

    Re: Delete values in one column based on values in a second column

    Thank you. I will test your proposed solution with my real data but I would prefer a solution along the lines I suggested.

  4. #4
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,660

    Re: Delete values in one column based on values in a second column

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    10-13-2022
    Location
    Northamptonshire, England
    MS-Off Ver
    Excel 2016
    Posts
    56

    Re: Delete values in one column based on values in a second column

    Thank you, again! I will test both solutions and step through them to make sure they are doing what is intended. I'll set up data for unit testing and let you know what happens.

  6. #6
    Registered User
    Join Date
    10-13-2022
    Location
    Northamptonshire, England
    MS-Off Ver
    Excel 2016
    Posts
    56

    Re: Delete values in one column based on values in a second column

    Neither of those approaches works in my situation. I am attaching a workbook with some typical data.

    Note that the values in columns A and D are always in ascending order. The data in columns B and E are in ascending order until their values are greater than 20, after which the values are somewhat random.

    I hope this helps. Please let me know if any further explanation is needed,
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    08-08-2022
    Location
    Buenos Aires
    MS-Off Ver
    Excel 2019
    Posts
    1,777

    Re: Delete values in one column based on values in a second column

    Quote Originally Posted by Paul Newton View Post
    Neither of those approaches works in my situation. I am attaching a workbook with some typical data.
    Hello. Your comment that I attach here complicates the interpretation of your need.

    Therefore, I suggest you upload a workbook to the Forum where we can clearly see what result you intend to get (obtained by hand of course) from your own data.
    You are always very welcome if you add reputation by clicking the * (bottom left) of each message that has helped you.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Delete values in one column based on values in a second column

    Quote Originally Posted by Paul Newton View Post
    One possible approach (which I have not been able to implement) might be to find the row number (N) of the first cell in the second column which meets the criteria (> 20 or <0) and then set the range to be deleted to be "A" & N & ":A" - assuming the left column is "A"
    try
    Please Login or Register  to view this content.
    Last edited by jindon; 12-18-2022 at 11:48 PM. Reason: add error trap for multiple run

  9. #9
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,660

    Re: Delete values in one column based on values in a second column

    Re: "Neither of those approaches works in my situation."
    They both work for me on your attachment. Of course the loop does not delete the cells in Column A where the values in column B are between 0 and 20. After all, that's what you asked for.
    Otherwise your request should have been to delete everything from the first found value that is < 0 and > 20 on down.
    Last edited by jolivanes; 12-19-2022 at 01:05 AM.

  10. #10
    Registered User
    Join Date
    10-13-2022
    Location
    Northamptonshire, England
    MS-Off Ver
    Excel 2016
    Posts
    56

    Re: Delete values in one column based on values in a second column

    I don't understand - you suggest that I upload a workbook - I already did (ExcelForum.XLS)

  11. #11
    Registered User
    Join Date
    10-13-2022
    Location
    Northamptonshire, England
    MS-Off Ver
    Excel 2016
    Posts
    56

    Re: Delete values in one column based on values in a second column

    Thanks to everybody for their contributions. I realised, that because of the way the data is set up, I know the row number of the last entry (in columns A and B or columns D and E) and so the following works very nicely

    Please Login or Register  to view this content.
    Now I need to ask another question about using a named range in defining a chart series ...

    Thanks again.

+ 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] Delete rows based on duplicate column values?
    By Casey825 in forum Excel General
    Replies: 11
    Last Post: 12-19-2017, 07:10 PM
  2. Delete rows based on values in a column
    By Motox in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-28-2013, 10:00 PM
  3. Replies: 8
    Last Post: 08-08-2013, 08:45 AM
  4. Delete Rows based on values present in another column
    By mlogan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-02-2013, 03:13 AM
  5. [SOLVED] sort and delete based on the values in column c and column j?
    By barbibchn in forum Excel General
    Replies: 1
    Last Post: 01-14-2013, 04:25 PM
  6. Delete rows based on Column B Values
    By vijanand1279 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-20-2011, 01:49 PM
  7. Delete Rows based on Values in a column
    By Recycle_Bin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-21-2009, 04:36 PM

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