+ Reply to Thread
Results 1 to 10 of 10

Deleting data in a range of cells based on if there is data in a cell outside the range

  1. #1
    Registered User
    Join Date
    08-01-2013
    Location
    Morgantown, WV
    MS-Off Ver
    Excel 2010
    Posts
    22

    Deleting data in a range of cells based on if there is data in a cell outside the range

    I am attempting to make a range of cells' data be deleted if a cell outside of that range that had a name in it is no longer there. So if I have a list of names in cells A1:A6 and a range of data in cells D10:E20 that corresponds to a name in cell A2. If the name in A2 is deleted I would want the data in D10:E20 to be deleted. If anyone can help me I would really appreciate it.

  2. #2
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,147

    Re: Deleting data in a range of cells based on if there is data in a cell outside the rang

    You can have a macro do it for you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    08-01-2013
    Location
    Morgantown, WV
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Deleting data in a range of cells based on if there is data in a cell outside the rang

    I do not have much experience in macros, do you have one in mind?

  4. #4
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,147

    Re: Deleting data in a range of cells based on if there is data in a cell outside the rang

    You can try this - i couldnt get it done any other way so used the double click option. You double click on the cell which you want deleted and the macro will take care of the rest -

    Right click on the sheet name which contains the data, select View Code and copy this code into the window that appears -
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-01-2013
    Location
    Morgantown, WV
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Deleting data in a range of cells based on if there is data in a cell outside the rang

    Just wondering where it says "Target", "Cell", "range", "value" or "dtext" if I should be putting in values there and if so what values?

  6. #6
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,147

    Re: Deleting data in a range of cells based on if there is data in a cell outside the rang

    No you do not need to put values in the code. You should just double click the cell which you need cleared from A1:A6 and the rest will be taken care of by the macro.

  7. #7
    Registered User
    Join Date
    08-01-2013
    Location
    Morgantown, WV
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Deleting data in a range of cells based on if there is data in a cell outside the rang

    When I just copy and paste that code in, I click the play button, which is what assumed made the program run. Once I clicked that a prompt came up asking me to name the macro and create it and once I did that, A new sub was started and mine did not run. I am just confused as to how to get the code to start working.

  8. #8
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,147

    Re: Deleting data in a range of cells based on if there is data in a cell outside the rang

    You need to right click on the sheet and select View code. Copy the code i provided you. Then just double click on the cells in column A in the range 1:6 and the corresponding cells will get deleted in the other range.

  9. #9
    Registered User
    Join Date
    08-01-2013
    Location
    Morgantown, WV
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Deleting data in a range of cells based on if there is data in a cell outside the rang

    Yeah that isn't working at all. Have any another suggestions?

  10. #10
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,147

    Re: Deleting data in a range of cells based on if there is data in a cell outside the rang

    It worked when i ran it. Can you attach a sample of the file you are using so i can check?

    You wanted the macro to delete the entries in the 2nd range once you delete the entries in the 1st range right? In this case, you do not need to delete but double click the entries in the 1st range and automatically the same entry in the 2nd range would be deleted. The double click will delete the entry in the 1st range too.

+ 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. Sum cells in a range based between two equal cell values in another range
    By sshone1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-09-2013, 08:12 AM
  2. [SOLVED] Copy data from a range of cells into a blank range based on common cell
    By vanmeterkj in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-03-2012, 10:18 AM
  3. [SOLVED] Change Cell Value Based on Data in Range of Cells
    By Dahaynes in forum Excel General
    Replies: 11
    Last Post: 03-28-2012, 12:13 PM
  4. How to define a range based on first and last cells with data?
    By Dennis in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-01-2006, 09:50 AM
  5. Copy range of cell data to range of cells arranged by groups
    By Wayne Makeeff in forum Excel General
    Replies: 0
    Last Post: 01-27-2005, 12:17 AM

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