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.
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.