Is there any way in Microsoft Excel that two or more consective blank rows converted to one blank row

Question

I have MS Excel 2007 document where i have many blank lines between data. i want that if there are more than one blank lines then all others are deleted but only one blank line is left

Answer 1

Below is one solution that you can use to automatically eliminate blank lines.
If you need to replace multiple blank lines by one, you will probably need a macro. See this article for an example that you can adapt : How to merge duplicate rows in Excel.

From Excel 2007: Eliminate Blank Rows in a Spreadsheet

Blank rows can be beneficial when it comes to making your spreadsheet easier to read, however if you are going to pull the data into another application (such as Access), the blank rows can cause you some problems. You could go through the painful task of selecting and removing these rows individually, but if you are working with a large spreadsheet, it could take forever to accomplish this. Instead, utilize Go To Special and let Excel do the work for you. Here’s how:

1. Select the range of data that contains the blank rows.
2. Go to the Ribbon, and select the Home tab.
3. In the Editing section, click the small arrow next to Find & Select.
4. Select Go To Special.
5. Select the Blanks radio button and click OK.
6. All blank rows within the selected data range will be highlighted.
7. Go to the Ribbon.
8. Click the small arrow beneath Delete.
9. Select Delete Sheet Rows

Answer 2

You could try the following: * create a new column (I call it A in the following) * enter numbers 1,2,3 .. in the cells of that column. So this is basically a rownumbr * create another column (I'll call it B) * fill the column with a little function: returning the value of A in the same row, when the row above is not empty or the own row is not empty, and a huge number otherwise. (huge must be bigger then the number of rows you have, including all blanks) * Now sort the complete sheet by column B. the double blank rows should be at the end of the sheet, while everything else is unchanged. * remove the columns A + B