+ Reply to Thread
Results 1 to 16 of 16

How do I delete blank rows in a worksheet

  1. #1
    Registered User
    Join Date
    04-08-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    30

    How do I delete blank rows in a worksheet

    I have been trying to efficently delete blank rows in a spreadsheet that I will need to work regularly (New versions of the spreadsheet every month). I have tried to do it using the go to blanks command (F5, special, select blanks, and then delete rows), but it has unexpected results - sometimes it is deleting actual data. Does anyone know a better way to do this?

    Thanks!

    I am attaching the spreadsheet I am using as a reference.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: How do I delete blank rows in a worksheet

    Select all data and sort it...
    Elegant Simplicity............. Not Always

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How do I delete blank rows in a worksheet

    based on your sample data, none of your rows are empty, in each row, at least 1 column has data in it.

    what exactly are you trying to do? if you use something like this, copied down, you can filtre on XXX and then delete those rows...then remove the filter...
    =IF(COUNTA(A1:G1)=0,"XXX","")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    04-08-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: How do I delete blank rows in a worksheet

    Sorry. I did not line the data up yet. I have attached the spreadsheet again. Also, I wasn't clear enough. I am trying to do this through a macro since I will have to do this on about 50 different workbooks each month, and then the plan is to enter the data in a database after it is cleaned up.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: How do I delete blank rows in a worksheet

    In which column/s are the empty cells? You have empty cell in most cells, but not an entire row is empty

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How do I delete blank rows in a worksheet

    there are still no empty rows in the new attachment?

  7. #7
    Registered User
    Join Date
    04-09-2013
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003, 2010
    Posts
    10

    Re: How do I delete blank rows in a worksheet

    Quote Originally Posted by FDibbins View Post
    there are still no empty rows in the new attachment?
    This is just a shot in the dark, but if you delete C2:G2 (and shift cells up) the data lines up and seems to create the blank rows the OP might be refering to. OP would have to verify that though.

  8. #8
    Registered User
    Join Date
    04-08-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: How do I delete blank rows in a worksheet

    I am so sorry. My brain is clearly not working today. Please see attached.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: How do I delete blank rows in a worksheet

    If blanks are in column A and want to delete an entire row

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    04-08-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: How do I delete blank rows in a worksheet

    Thanks AB33. However, that won't work for me because there are rows that are blank in column A, but have data in other columns. Because of that, if I use the macro you provided, it ends up deleting data that I would need to keep.



    Quote Originally Posted by AB33 View Post
    If blanks are in column A and want to delete an entire row

    Please Login or Register  to view this content.

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How do I delete blank rows in a worksheet

    Quote Originally Posted by FDibbins View Post
    if you use something like this, copied down, you can filtre on XXX and then delete those rows...then remove the filter...
    =IF(COUNTA(A1:G1)=0,"XXX","")
    Use that formula, then use that column to base whatever other method you use to delete the rows

  12. #12
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: How do I delete blank rows in a worksheet

    Please do not reply with quote. It has not purpose other than to clutter the space of this page.
    So, an entire row has to be empty in order to delete a row, Is this right?

  13. #13
    Registered User
    Join Date
    04-08-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: How do I delete blank rows in a worksheet

    My apologies on the quote. Yes, the entire row has to be empty in order to delete the row. Thanks.

  14. #14
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: How do I delete blank rows in a worksheet

    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    04-08-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: How do I delete blank rows in a worksheet

    Worked perfectly. Thanks!

  16. #16
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: How do I delete blank rows in a worksheet

    Harry,
    You are 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