Remove Blank Rows in Excel (5 Ways + VBA)

by

A dataset in Excel containing blank rows is challenging to read and navigate and can lead to computational errors.

Therefore, we must remove the empty rows to resolve such problems. This tutorial shows you five techniques for removing blank rows in Excel.

Note: We recommend you back up your workbook before applying any of the following methods to remove rows from your dataset. This safeguard ensures that you can revert to your original data in case data is deleted unintentionally. 

Method #1: Remove Blank Rows By Manually Selecting and Deleting

In this method, we manually select all the blank rows and delete them. 

We use the following dataset, which has three blank rows (4, 8, and 12), to illustrate how this technique works. 

data set with blank rows

To delete the blank rows 4, 8, and 12 from the dataset, use the following steps:

  1. Click the numeric header of row number 4 to select the entire row.
select the entire row
  1. Press and hold down the Control key, click the numeric row headers of rows 8 and 12 to select the rows, and then release the Control key.
select all the blank rows one by one
  1. Right-click the selection and click Delete on the shortcut menu that appears.
right click on the blank rows and click on delete

The three blank rows are removed from the dataset:

blank rows have been removed

Note: This manual method is okay for small datasets but inconvenient for large ones with tens or hundreds of blank rows. 

We recommend removing blank rows from large datasets using the following techniques.

Also read: How to Remove Blank Columns in Excel? (Formula + VBA)

Method #2: Remove Blank Rows Using Find and Replace + Formula

This method involves using a formula and the Find and Replace dialog box to select all the blank rows from the dataset and then delete them.

We use the following dataset, which has three blank rows (4, 8, and 12), to illustrate how this technique works. 

data set with blank rows

We use the below steps:

  1. Insert a helper column by selecting column A (click the column header), right-click the column, and select Insert on the shortcut menu that appears. 
insert a new row at the top
  1. Select cell A1 in the helper column and type in the following formula:
=IF(COUNTA(B1:XFD1)=0,"Blank","Not Blank")
enter the Formula to check for blank columns
  1. Click the Enter button on the Formula bar to enter the formula.
enter the formula
  1. Drag the fill handle to copy the formula down the column.
apply the formula to the entire first column

Notice that the formula has returned “Blank” in the first cell of all the blank rows (4, 8, and 12).

  1. Select the cell range A1:A14 and press Ctrl + F to open the Find and Replace dialog box.
  2. On the Find and Replace dialog box that appears and do the following:
  1. Open the Find tab.
  2. Type “Blank” (without quotation marks) in the Find what box.
  3. Open the Within drop-down and select Sheet.
  4. Open the Search drop-down and select By Rows.
  5. Open the Look in drop-down and choose Values.
  6. Select the Match entire cell contents option.
  7. Click the Find All button.
find all the cells with the word blank in it

The Find and Replace feature finds all the cells that contain “Blank” and displays their cell references at the bottom of the dialog box.

all the cells with the word blank are found
  1. Press Ctrl + A to select all the cells that contain “Blank.”

The cell references at the bottom of the Find and Replace dialog box are highlighted in blue, and the cells containing “Blank” in the dataset are selected.

select all the cells with the word blank in it
  1. Click the Close button to close the Find and Replace dialog box.
  2. Right-click any of the selected cells in the dataset and select Delete on the shortcut menu that appears:
right click and delete the rows
  1. Select the Entire row option on the Delete dialog box that appears and click OK.
Click the Entire Row option

All the blank rows are deleted, and data is shifted upwards:

all empty rows have been removed
  1. Delete the helper column.
delete the helper column

We now have our dataset minus the blank rows.

final data set

Explanation of the formula

=IF(COUNTA(B1:XFD1)=0,”Blank”,”Not Blank”)

The formula combines the IF and COUNTA functions.

The COUNTA function computes the number of non-empty cells in the range B1:XFD1.

The function begins counting from cell B1 because cell A1 contains a formula and is part of the helper column.

The function stops counting at cell XFD1 because that is the last cell in the row and is part of column XFD which is the last column in the worksheet. 

If the value returned by the COUNTA function equals zero (0), the row is blank, and the IF function returns the value “Blank.”

Conversely, if the number returned by the COUNTA function is not equal to zero (0), the row is not blank, and the IF function returns “Not Blank.”

Also read: How to Delete Multiple Rows in Excel?

Method #3: Delete Blank Rows Using Formula + Sorting 

In this method, we use a formula to identify the blank rows and then use the Sort option to group them before deleting them.

This method is handy if you want to delete non-adjacent blank rows. 

We use the following dataset, which has three blank rows (4, 8, and 12), to illustrate how this technique works. 

data set with empty rows

We use the following steps:

  1. Insert a helper column by selecting column A (click the column header), right-click the column, and select Insert on the shortcut menu that appears. 
insert helper column
  1. Select cell A1 in the helper column and type in the following formula:
=IF(COUNTA(B1:XFD1)=0,"Blank","Not Blank")
if formula to find blank rows
  1. Click the Enter button on the Formula bar to enter the formula.
enter the formula
  1. Drag the fill handle to copy the formula down the column.
copy the formula for the entire column

Notice that the formula has returned “Blank” in the first cell of all the blank rows (4, 8, and 12).

  1. Select the cell range A1:A14 in the helper column.
  2. On the Data tab, in the Sort & Filter group, click the Sort button.
click the sort icon in the ribbon
  1. Select the Expand the selection option on the Sort Warning dialog box that appears and click the Sort button.
click on expand the selection
  1. Click the Options button on the Sort dialog box that appears.
click on the options button in the sort dialog box
  1. Select the Sort top to bottom option on the Sort Options dialog box that appears, and then click the OK button.
select sort top to bottom option
  1.  Do the following on the Sort dialog box:
  1. Open the Column Sort by drop-down and select Not Blank (This is the header of the helper column).
  2. Open the Sort on drop-down and select Cell Values.
  3. Open the Order drop-down and choose A to Z.
  4. Click OK.
sort based on the blank column

All the blank rows are grouped at the top of the dataset:

all blank rows are stacked at the top
  1.  Select row 2 by clicking its numeric row header, press and hold down the Shift key, select row 4, which is the last blank row, then release the Shift key.
select all the blank rows
  1.  Right-click the selected blank rows and select Delete on the shortcut menu that appears:
delete all the empty rows

The blank rows are deleted, and the data is shifted upwards:

data set after blank rows have been deleted
  1.  Delete the helper column.
delete the helper column

The helper column is deleted, and data is shifted to the left.

We now have our dataset without the blank rows.

final data set

Method #4: Remove Blank Rows Using Power Query

We can use Excel’s Get & Transform feature to remove blank rows from a dataset.

This feature, however, converts the source range into an Excel table and changes the formatting, such as number formats.

Therefore, if you want to keep your dataset’s original formatting, use the other methods in this tutorial.

We use the following dataset, which has three blank rows (4, 8, and 12), to demonstrate how this method works. 

data set with Blank rows

We use the below steps:

  1. Select the entire dataset.
  2. On the Data tab, in the Get & Transform Data group, click From Table/Range.
click the from table/range option

The Create Table dialog appears.

Notice that the Get & Transform Data feature has correctly guessed the dimensions of the data range and its headers.

If the guess is wrong, you can make the needed adjustments. 

create table dialog box
  1. Click OK on the Create Table dialog box.

The dataset is converted to an Excel Table, and the Power Query Editor is launched. 

  1. On Power Query Editor’s Home tab, in the Reduce Rows group, open the Remove Rows drop-down and select the Remove Blank Rows option.
click on the remove all blank rows from this table option
  1. Click the Close & Load button. 
click on close and load button

The Power Query Editor is closed, and the resulting table is loaded to a new worksheet. 

Method #5: Delete Empty Rows Using Excel VBA Script

This method involves creating a sub-routine in Excel VBA and then running it to delete empty rows from a dataset.

We use the following dataset, which has three blank rows (4, 8, and 12), to show how this technique works. 

data set with blank rows

We use the following steps:

  1. Open the active worksheet containing the dataset.
  2. On the Developer tab, in the Code group, click Visual Basic to open the Visual Basic Editor.
click the visual basic icon

Alternatively, press Alt + F11 to open the Visual Basic Editor.

  1. In the Visual Basic Editor, open the Insert menu and select Module to insert a module.
click on the module option
  1. Please copy the following code and paste it into the module.
'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub removeBlankRows()
Dim focusRange As Range
Dim wholeRow As Range
Dim i As Long
  On Error Resume Next
  Set focusRange = Application.InputBox("Select a range:", "Remove Blank Rows", Application.Selection.Address, Type:=8)
  If Not (focusRange Is Nothing) Then
    Application.ScreenUpdating = False
    For i = focusRange.Rows.Count To 1 Step -1
      Set wholeRow = focusRange.Cells(i, 1).EntireRow
      If Application.WorksheetFunction.CountA(wholeRow) = 0 Then
        wholeRow.Delete
      End If
    Next
    Application.ScreenUpdating = True
  End If
End Sub

  1. Save the sub-routine and the workbook as a Macro-Enabled Workbook.
  2. Place the cursor anywhere in the sub-procedure and press F5 to execute the code. 
  3. When the Remove Blank Rows dialog box appears, open the active worksheet and select the entire dataset.
select a range from which you want to delete the blank rows
  1. Press OK on the Remove Blank Rows dialog box, and you are taken back to the Visual Basic Editor.
  2. Click the View Microsoft Excel button on the toolbar to switch to the active worksheet.
go back to the worksheet

We can also switch to the active worksheet by pressing Alt + F11.

When we look at the dataset, we see that the sub-procedure has deleted all the blank rows:

empty rooms have been deleted

This tutorial has shown five techniques for removing blank rows in Excel. We hope you found the tutorial instructive and helpful.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

1 thought on “Remove Blank Rows in Excel (5 Ways + VBA)”

  1. It’s still a hoot to learn something new like this, outstanding insights – I’m bookmarking your page. Thank you, Steve. I’ve managed a few tricks myself to expedite project tasks in various environments, but it’s hard to beat Excel for being the shortest path to getting the results ya need. Thanks for sharing.

    Reply

Leave a Comment