1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel FOrmula conditional delete

Discussion in 'Business Applications' started by ksquared, May 30, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. ksquared

    ksquared Thread Starter

    Joined:
    Apr 19, 2004
    Messages:
    68
    Hi,

    I need help with a formula for an excel spreadsheet.

    If the contents of a particular cell (in my example r1) = 0, than delete the entire line (in my example it would be line 1)


    Running XP, using MS Office 2007

    Thanks in advance.
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi,

    You will probably have to do this using a macro.

    Loop through the column R and when the the value in Rx = 0 then Range("R" & x).EntireRow.Delete

    where x is the row number
     
  3. ksquared

    ksquared Thread Starter

    Joined:
    Apr 19, 2004
    Messages:
    68
    Thanks Hans. I'm not very good at writing macros from scratch. What I normally do is capture my key strocks and let the developer program build the macro for me.

    Can you help with the excel part of things. I'm thinking a vlookup might work?

    Example: =vlookup (if Rx=0, delete the row) This is not a formula that works of course. Just the way I say it if I were talking.
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Try this:

    Code:
    Sub DeleteConditional()
    Dim lstRow as long, xRow as long
    lstRow = Range("R" & Rows.Count).End(XlUp).Row
    For xrow = lstRow to 2 step -1
         If Range("R" & xrow).value = 0 then Range("A" & xrow).EntireRow.Delete
    Next xRow
    End Sub 
    
    This just a simple macro, no testing if it's the correct sheet, just starting from the last row en going up it will delete the entire row if the value in Colum R row x = 0

    Make sure you make a copy of your file before trying it out.

    From here on you can build on.
     
  5. ksquared

    ksquared Thread Starter

    Joined:
    Apr 19, 2004
    Messages:
    68
    Perfect! I added it to the existing macro that I was already using to do some other things and it works. Thank-you so much :) Very much appreciated.

    Cheers,
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    You're welcome :)
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1055206

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice