+ Reply to Thread
Results 1 to 11 of 11

Delete rows (or cells) where payments offset sales

  1. #1
    Registered User
    Join Date
    12-13-2012
    Location
    Kitchener, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    39

    Delete rows (or cells) where payments offset sales

    Hey all,
    I have some VBA code that I need modified.
    Please see attached spreadsheet.

    The VBA code is meant to delete all rows where the cash received in column I offsets the sales in column H.
    For the cells highlighted in blue, it does the trick.
    However, I need the code modified so that it works with both the blue and the red cells.
    I guess the rows can’t be deleted if there is a sale amount beside it that the payment does not offset.
    Can anyone help?
    See code below:

    Please Login or Register  to view this content.
    payments that offset sales.xlsm
    Last edited by bwaite87; 04-12-2013 at 11:51 AM.

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Delete rows (or cells) where payments offset sales

    What do you mean by
    offsets the sales in column H
    Do you mean you want to delete the rows where the value in column I is equal to or greater than the value in column H?

  3. #3
    Registered User
    Join Date
    12-13-2012
    Location
    Kitchener, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Delete rows (or cells) where payments offset sales

    not greater than, just equal to. But yes

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Delete rows (or cells) where payments offset sales

    I'm still a little confused. I can't see any values in column I that are equal to the cell to the left in column H. Can you give me a couple of specific examples from your spreadsheet that meet your criteria to delete the row?

  5. #5
    Registered User
    Join Date
    12-13-2012
    Location
    Kitchener, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Delete rows (or cells) where payments offset sales

    no problem, below is what the macro should do:

    Cell H2:H6 is offset by the payment in I6. Delete rows 2 through 7
    Cell H8:H9 is offset by the payment in I9. Delete rows 8 and 9
    Cell H15:H19 is offset by the payment in I21. Delete rows 15 through 19 and change Cell I21 to zero
    Cell H25:H26 is offset by the payment in I25. Delete rows 25 through 27.

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Delete rows (or cells) where payments offset sales

    Thank you for being so patient. I believe that your statement
    I guess the rows can’t be deleted if there is a sale amount beside it that the payment does not offset.
    describes the real problem. I can't seem to find a solution to this problem. Perhaps there is someone else in the forum with more expertise than can suggest a solution. Sorry that I wasn't able to help.

  7. #7
    Registered User
    Join Date
    12-13-2012
    Location
    Kitchener, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Delete rows (or cells) where payments offset sales

    no problem, thanks for trying!

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Delete rows (or cells) where payments offset sales

    Hi BWaite,

    I'll take a look at this soon.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  9. #9
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Delete rows (or cells) where payments offset sales

    Hi BWaite,

    Try this:

    Please Login or Register  to view this content.
    Last edited by xladept; 04-12-2013 at 09:09 PM.

  10. #10
    Registered User
    Join Date
    12-13-2012
    Location
    Kitchener, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Delete rows (or cells) where payments offset sales

    ...you're a genius!
    Thanks so much xladept

  11. #11
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Delete rows (or cells) where payments offset sales

    You're welcome!

+ 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