+ Reply to Thread
Results 1 to 14 of 14

Delete row if cell in range equals zero

  1. #1
    Registered User
    Join Date
    06-27-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    26

    Question Delete row if cell in range equals zero

    So I need to delete any row that has a value of either 0 or blank in my range of J7:J1006. I am trying to avoid an if/then statement to reduce run time. This macro is extremely quick for the blanks which I really like:

    Please Login or Register  to view this content.
    I have been trying to find how to alter the xlCellTypeBlanks to select any cells with 0 but I have not been able to. Is this possible? If not, how would you delete the cells with 0? I don't need a macro for the blanks since this one works great.
    Last edited by perdooky; 04-17-2012 at 01:09 PM.

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Delete row if cell in range equals zero

    Try something like this:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-27-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Delete row if cell in range equals zero

    I added a 0 in the Criteria because I need it for zeros. This is only deleting the first zero it finds and then the macro is stopping, looks to be on the right track though...

  4. #4
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Delete row if cell in range equals zero

    is the filter working for all 0's? or is that also causing an issue? after the macro runs the data should remain filtered.

  5. #5
    Registered User
    Join Date
    06-27-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Delete row if cell in range equals zero

    Doesn't look like it, I just ran this:

    Please Login or Register  to view this content.
    And it filtered almost all data including positive and negative numbers that weren't even 0

  6. #6
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Delete row if cell in range equals zero

    could you attach a sample book?

  7. #7
    Registered User
    Join Date
    06-27-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Delete row if cell in range equals zero

    Sample booked attached
    Attached Files Attached Files
    Last edited by perdooky; 04-17-2012 at 01:01 PM.

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

    Re: Delete row if cell in range equals zero

    Try it with Val(ActiveCell)=0 after selecting it
    i.e. If Val(ActiveCell)=0 Then ActiveCell.EntireRow.Delete

  9. #9
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Delete row if cell in range equals zero

    Give this a go:

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    06-27-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Delete row if cell in range equals zero

    Works, thanks a lot, can you explain what it's doing? I'd appreciate it.

  11. #11
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Delete row if cell in range equals zero

    it is filtering by blanks and - (i cheated and used macro recorder to filter) then just deleting all visible rows in that range.

  12. #12
    Registered User
    Join Date
    06-27-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Delete row if cell in range equals zero

    Oh nice, looks like I can skip the step of deleting blanks then since this takes care of both blanks & zeros. Appreciate it!

  13. #13
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Delete row if cell in range equals zero

    glad to assist, good luck with the rest of the project.

  14. #14
    Registered User
    Join Date
    06-27-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Delete row if cell in range equals zero

    My headers were being deleted so I made the following changes:

    Please Login or Register  to view this content.

+ 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