Remove empty rows from selection using VBA.

Often I come across this situation where I have a few empty cells in between. Like Consider a blank sheet and then you paste some values into it in Column “A”. ┬áLike in A1: January, A2: February, A3,A4&A5 are blanks, then A6: March, A7,A8 are blank, A9: April, A10: May, A11,A12 and A13 are blanks….. and so on…

Imagine this sequence runs down to 80 or 100 rows then its a pain to delete the blank rows in between. A trendy excel user might use the paste special technique of skip blanks and accomplish this. But a more easier way is a single line of code that helps remove all blanks rows. The code goes like this.

sub deleteblanksrows()

Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End sub

Now copy this code and paste it in a module (click ALT+F11 to go to vba editor and from Insert menu click Module-then paste this in the blank white space on right).

Go back to excel and select the selection which contains blank rows say in our case above it would be range A1:A13. Go back to VBA editor by hitting ALT+F11 and then keep the cursor on the 2nd line of the code and hit f5 to run the code.

That is it switch back to excel and you will see all the blank rows are gone.

I work with unformatted  data a lot and I use this code quite often so I have placed this on the ribbon of my excel 2010.

Hope you will also benefit from this VBA snippet.

17 thoughts on “Remove empty rows from selection using VBA.

  1. I do not even know the way I finished up right here, but I believed this put up was once good. I don’t realize who you might be but definitely you are going to a famous blogger when you aren’t already ;) Cheers!

  2. I have realized some new points from your web page about desktops. Another thing I’ve always assumed is that computer systems have become a specific thing that each home must have for a lot of reasons. They offer convenient ways in which to organize the home, pay bills, go shopping, study, pay attention to music and in some cases watch tv programs. An innovative way to complete all of these tasks is a computer. These desktops are mobile, small, highly effective and lightweight.

  3. I have to find around my passion for the kindness promoting people who really need benefit el born area of interest. Your own real dedication to moving the solution close to had been extremely good and possess often prompted staff at all like me to arrive at cause real progress. Your personal valuable valuable information implies close to this much somebody at all like me but still far more to be able to my associates. Many thanks all individuals.

  4. I like the helpful information you provide in your articles. I will bookmark your weblog and check again here regularly. I am quite sure I will learn lots of new stuff right here! Good luck for the next!

  5. I have learned newer and more effective things out of your blog post. One other thing to I have noticed is that usually, FSBO sellers can reject people. Remember, they’d prefer not to ever use your companies. But if anyone maintain a steady, professional romance, offering assistance and being in contact for about four to five weeks, you will usually have the ability to win a meeting. From there, a listing follows. Many thanks

  6. When I first saw this title Remove empty rows from selection using VBA. | Techflame's Official Blog on google I just whent and bookmark it.Hey Now there. I found your site using live messenger. This will be the very well crafted article. We will make confident to help bookmark that and return to see more of the useful info. Thanks with the post. I actually will definitely comeback.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>