How to bulk delete Excel blank lines

We use Excel statistics and analysis of objects, data input work can not be achieved overnight, record add, delete, cut, merge is indispensable, after many edits to modify the process, in the table will often appear many empty lines. The presence of these empty lines is both unattractive and affects the results of data analysis, which are usually deleted. If there are fewer empty rows in the table, you can delete them one line at a time, but if the worksheets are large (such as thousands of rows), and many of the empty rows in the table are blank, manually deleting them in one row is a time-consuming effort. If you often encounter this kind of trouble, try these methods, you can quickly, cleanly, batch to clean out the nominal empty line.

"Positioning" method

Edit the form, when you need to delete the empty line, click on the menu bar "edit/Position", in the pop-up "positioning" dialog box, click the "Positioning Criteria" button. In the next Pop-up positioning Criteria dialog box, select the null value radio by item (Figure 1) and click OK. OK, now look again, all the blank lines in the table are all selected and shown in blue. Click "Edit/Delete" in the menu bar to see that the blank line in the table has disappeared without a trace.

Figure 1

Tip: When you use this method to delete, make sure that all cells in other non-empty rows are filled with values, otherwise empty values in non-null rows are deleted, which directly results in a completely confusing table format.

"Filter" method

If there are items in the table that are not filled in, you must filter the blank rows of the table before you delete them by using the filter feature.

First step: First position the mouse over the top of the worksheet, and then insert a new blank row in the table. Then insert a new empty column at the leftmost edge of the worksheet, enter "1" in the first single grid, and then drag the automatic fill handle to populate the previous sequence with the column. This is done primarily to prepare for the next step of screening, because Excel can only recognize contiguous rows while filtering, encountering empty rows does not continue filtering down, so before filtering, we insert a column in the table with the most left in it, filling a simple sequence in the column to make the table contiguous.

Step two: Next, press the "CTRL + a" key to select the entire worksheet. Click the data/Filter/AutoFilter command in the menu bar.

Step three: At the top of each column, a drop-down list box appears, select a column, click its drop-down list box, and choose Blank. If there are rows of data in the page, select another column and choose Blank from its Drop-down list box until the data is not visible in the page (Figure 2).

Figure 2

The fourth step: OK, this time the rest of the page is blank line, select the "Edit" menu, choose "Delete Rows" command on it.

Step Fifth: When all the blank lines have been deleted, and then click Data/Filter/AutoFilter, the data on the worksheet is restored, and then the column that you inserted on the left is deleted.

Tip: To insert a blank line at the beginning of the operation, one is to avoid deleting the first row of data. The second is that when the worksheet has a header row, the Drop-down list box appears only when the AutoFilter command is selected.