Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Rows with Zeros (97sr2)

    I have a column of data - 50K rows. Most of the data is zeros. But, there are about 9000 cells that have data in them. I want to know the fastest, easiest way of deleting all the rows with zeros so that all I have left are the rows with data.

    Example:
    0
    0
    0
    15
    0
    0
    0
    0
    0
    6

    Delete all zeros and have a column that has values greater than zero.

    How can I do this the easiest? I don't know code.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows with Zeros (97sr2)

    DUH! Sometimes I make things much more difficult than they need to be. So sorry for the waste of a post. I sorted the data .... ahhh.

  4. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,820
    Thanks
    0
    Thanked 167 Times in 154 Posts

    Re: Delete Rows with Zeros (97sr2)

    Of course, if you want to retain the original data order, sorting might not work ...

    The following macro goes through column A, deleting any rows that have zero values, are blank or have text (eg tick marks or dashes):

    Sub CleanUp()

    Dim A As Long
    Dim C As Range

    On Error GoTo Abort
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Set C = ActiveSheet.UsedRange.Rows
    For A = C.Rows.Count To 1 Step -1
    If Application.WorksheetFunction.Sum(C.Rows(A).Column s("A:A")) = 0 Then
    C.Rows(A).EntireRow.Delete
    End If
    Next A

    Abort:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    End Sub

    PS: Once you've added it to the worksheet, it'll beat sorting and deleting the unwanted rows any time.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #4
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Delete Rows with Zeros (97sr2)

    Another option is to use advanced filter with the criteria set to <0, then click the radio button to have filtered data copied to another part of the worksheet.
    This way you retain your original data and have the filtered data available to work with. HTH
    <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15><img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  6. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows with Zeros (97sr2)

    Thanks to both of you. What I ended up doing was adding an ID Number column in which I numbered the rows with a fill. Then I sorted on Column A (with the zeros) in descending order. Copied, pasted both colums of data (not including the zeros) to another part of the worksheet. Then I sorted by the id column (data was back in the original order). Not very elegant, but it was quick and worked great. I will, however, print out your suggestions for future use.
    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •