Suppose there are thousands of rows of data with intermittent blank rows that hinder analysis. We never want to select each blank row individually.

 

Data

A small data set from France Department of National Demographic Studies showing the impact of COVID-19 on households.

In this instance the data resides in rows 3 to 9.

Workbook download Remove Blank Rows

Always save a copy of your data if you are unsure of what would happen.

No Formula Method: Go To Special

The first step to select all data rows is to go to Name Box or follow the method on cell selection .

In Name Box, put “A3:A9” without quotation mark, then press Enter. This will select cells A3 to A9.

Press Ctrl+G to open Go To dialogue box, then press Special button.

In the window that come up, select “Blanks”, press OK.

The result should look like below

Press Ctrl+- (minus sign) to remove selected blanks. This will delete the entire blank rows.

Choose “Entire Row” in the option.

Formula Method 1: SORT

In a blank column next to data area, say cell D3, put the formula

=IF(C3<>””, MAX($D$1:D2)+1,””)

The formula checks if column C has value, then find the maximum value in the cells immediately above the active cell, add 1 to increment value. The formula allows the correct sort order.

Copy values in column D then again paste them as special (Ctrl+ALt+V), choose Paste Values.

Press Alt+D+S to active sort. Expand sort selection. Choose Column D in “Sort by” field. Afterwards, remove column D.

Formula Method 2: ISBLANK()

If the data is relatively simple, skip the formula step below, and simply filter for blanks. The formula method is great when you wish to retain certain rows, in which case it will need to be modified according to your need.

In a new column, say cell D3, put the formula

=ISBLANK(C3)

This will produce a column of TRUE and False once the formula extend to other rows by dragging the bottom right handle of the cell downwards.

Select cells A2 to D2, or simply click on row 2.

Activate the filter by going to Home tab, in Editing section, choose “Sort&Filter” then choose “Filter”. The shortcut key is Alt+H+S+F, or Alt+D+F+F.

Click on the filter dropdown, then select TRUE only.

Next select the filtered rows and press Alt+; (semicolon). This will select visible cells only and has the same effect as Go To Special introduced in the first method.

It will ask if you want to delete entire sheet row, select OK.

Next remove filter.

 

Google Sheet Method

No Formula Method and Formula Method 1: SORT both apply to Google Sheet.

To apply the No Formula Method, go to Data, then “Filter by values”. Select “Blanks”.

To apply SORT method instead, write the formula then go to Data, Filter views, Create new filter view.

Pin It on Pinterest

Share This