Comments on: How to remove all blank rows in Excel

In this quick tip I will explain why deleting Excel rows via select blank cells -> delete row is not a good idea and show you 3 quick and correct ways to remove blank rows without destroying your data. All solutions work in Excel 2016, 2013 and lower. Continue reading →

Comments page 3. Total comments: 129

  1. The 3rd option is what I tried and it works up to the point that I clear the filter and the screen seems to freeze. The data is still there but you can't seem to click on anything. I say "seems" because I found out that the program is still working like it should it's just that the screen doesn't refresh. I just click the X and save the work. When you return to the program it works correctly without the blank rows.

  2. Elegant! Thank you!

  3. Fantastic short cut and saves much time...thanks a lot and appreciated..

  4. Thanks

  5. Thank u great work

  6. very helpful.thanks a lot.

  7. Excellent post. Thanks a lot.

  8. Thank you. Very detailed explanation.

  9. Nice trustworthy tutorial over other faulty tricks shown on internet

  10. Didnt work for me. Tried it twice

  11. Thank you very much keep up the good work

  12. Very Helpful. Thank you.

  13. Oh my..this is so helpful. Thanks a lot!!!!

  14. Thanks A lot, it worked.
    You've just saved me an hour of dull work and taught me something new. Thanks again

  15. Option 3 works easy.Thankyou

  16. The only solution that works!Thank u man!

  17. Thank you :) Very Helpful

  18. Thank you :)

  19. Very useful tool, thanks a lot ,it is helpfull

  20. Very Nice post.

  21. Thanks :) it is very helpful

  22. Hello Team,

    Could you please hlep me to remove the blank columns(I have all header column names but no data). Here I have to remove no data columns(with name).

    Thanks in advance. :)

    LOkesh P

  23. Thanks for this informative tutorial

  24. Thank you very much saved a lot of time.

  25. Superb, especially with such a detail explaination.
    thx heaps :)

  26. Thank you!!

  27. Is there a way to remove blank rows by formulas instead of filtering?

  28. Thanks Alex, invaluable little tutorial, was having issues with the ubiquitous 'Go to Special >Blanks etc' that you refer to in your first paragraph. Nice to see a more considered approach :)

  29. It was very effective! Thanks a ton!! :)

  30. Thank you SOOOOOOOOOOO much. well described, great help!

  31. Thank you so much!!!

  32. very helpful

  33. Hi,

    I have all these extra lines below my data and it is making scrolling difficult. Is there a way to remove all of the lines below my data without them popping back up.

    Thanks.

  34. Good

  35. Thanks a lot friend.
    Appreciated.

  36. Thnks....
    really very helpful...

  37. thank you so much
    =countblanks() is very good way

  38. Hi,
    Excellent tutorial! One question I still have.... I have a sheet with 110,000 rows, with 35381 blank rows. When I try your second method "Remove blank rows using a key column" after I select the delete row option I get the following error:
    "Excel cannot create or use the data range reference because it is too complex. Try one or more of the following:
    - use data that can be selected in one contiguous rectangle
    - use data from the same sheet"

    obviously I'm working on the same sheet, and this method is only works because we're not selecting a contiguous rectangle....
    I'm wondering what my options are.

    Thanks in advance for your help,

    Francois

  39. good job

  40. thanks a ton

  41. Very Nice Macro

  42. Really useful to me and saved my lots of time.

  43. Its really useful to me and saved my lots of time.

  44. This crap doesn't work for large data sets, whats the point?

  45. Hi,

    In fact your tutorial was very helpful to me and I really thank you for that. Hope to learn a lot from you. Thanks once again.

  46. Thank u very much. Saved my precious time.

  47. is there a way to delete the rows on the bottom of my work sheet so i only see the data that i am finished with.

    1. I am sorry it is not very clear what result you want to get. Please send an example to support@ablebits.com

  48. Hello, When I tried to remove the empty rows using the Option 1,i.e (Apply filter to key column), the first row is also being deleted after the data is filtered.
    i.e I don't see the first row after deleting the empty rows. When I press Ctrl+Home after filtering, the first row is also getting included along with blank rows.
    Any idea , How to get rid of this??

  49. it really helped...many thanks..

  50. Thanks! I've been doing this the long way for far too long. This was excellent help!

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)