How to Delete Blank Rows in Excel (6 Ways)

Last updated on April 16th, 2018

While working with a big spreadsheet you often insert blank rows intentionally to read and understand it properly. Although inserting blank rows are liked by the majority of people, for some people working with blank rows can be very annoying. Sometimes blank rows can be generated due to some glitch in your Excel software. It can also occur while transferring the excel file, exporting data from the corporate database etc. In this article, I will be giving you some guidelines about how to delete blank rows in excel.

Eliminating Blank Rows Manually

The simplest way to delete/eliminate blank rows is to select the blank rows manually. This may take a long time in big spreadsheets, as you have to target them manually. In this process, select the blank row by just putting the mouse cursor in the leftmost side of a row and clicking the right button of the mouse. By this, you select the whole blank row, continuing this process for other blank rows will eventually select all the blank rows. Then in the Home tab under Delete option, press Delete Sheet Rows. By clicking it you will delete all the blank rows in your spreadsheet. The following pictures show the process.

Eliminating Blank Rows Manually

This process also works for the rows which aren’t blank. So, the process can be used in a variety of situations.

Deleting Blank Rows by finding Blank cells

If you have a spreadsheet containing a handful of blank rows deleting them manually will take so much time. So, let’s choose a way faster way to do this.

  • 1st select the area where you want to perform your task
  • Then in the Home tab under the Find & Select option click on to Go to Special

  • In the GO TO Special box click on the Blanks and press OK
  • This will select all the blank cells at once

Deleting Blank Rows by finding Blank cells

  • Now delete the cells just the way I did in the previous example

Note: As you can see, in this procedure all the rows which have blank cells were deleted. This is not the thing we want. There is another thing you should be careful about is that, if there are some missing values in any column they will be deleted as well so the data will displace and can cause inconsistency.

Remove Blank Rows by Using Excel Find Functionality

In this example, we modify our table a little, so that you can have a proper result. Because of too many blank cells in the previous examples, some rows which were not actually blank were deleted. This will not happen in this example.

Here, we will use the excel Find feature. The following is the step by step process for this.

  • 1st select your data set and in the Home tab under Find & Select option click on Find
  • Then in the Find and Replace box put nothing on Find what option and select values in Look in option

Remove Blank Rows by Using Excel Find Functionality

  • After pressing Find all an extension of that box will appear in below. Press CTRL+A to select them all then close the box

  • You will see the blank boxes in the selected zone. Delete them in the same way that we did in the previous examples

Using Filter Functionality to Delete Blank Rows

For this method, we need to put all our data in a table. Follow the below steps to do this method.

  • 1st select the range from which you will remove the blank rows
  • In the Home tab under the Sort & Filter option click on Filter

  • After this select, a column and click the filter drop-down, uncheck all the values except Blanks then press OK

  • This will select all the blank cells in the range

  • Then in the Home tab under Delete option, press Delete Sheet Rows
  • Finally, remove the filter and you will see unfilled rows are eliminated

Using Filter Functionality to Delete Blank Rows

Note: Make sure to make a table before doing this process otherwise you will not find every name in the column as there are blanks in between the rows of the columns.  By making a table for the cells you are indicating a range to do the operation.

Using If Statement in Excel to Delete Blank Rows

For this, we will make an extra column titled Test. This is quite similar to the previous example. The difference is that you can see the blank rows of your spreadsheet easily. The procedure is stated below.

  • On the Column of Title in cell D2 type this formula =IF(AND(ISBLANK(A2), ISBLANK(B2), ISBLANK(C2)),”Blanks”, “No-Blanks”)
  • After pressing enter you will see a result either Blanks or No-Blanks. Dragging this formulated cell to downwards will copy this formula to every cell

Using formula in Excel to Delete Blank Rows

  • Just like the previous example execute the filter option for the Test column and uncheck the No-Blank box

  • This will show the blank cells only. Now delete these blank cells like we did before then remove the filter option. You will see all the blank rows of the table has disappeared

Removing Blank Rows using Excel VBA

If you want to eliminate the blank rows using macros/VBA you can use the following code to do that. The code was collected from this link https://www.mrexcel.com/forum/excel-questions/1015878-macro-fro-removing-blank-cells-range.html

Sub DeleteBlankRows()
Dim Rw As Range
If WorksheetFunction.CountA(Selection) = 0 Then
MsgBox "No blank rows found", vbOKOnly
Exit Sub
End If
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
Selection.SpecialCells(xlCellTypeBlanks).Select
For Each Rw In Selection.Rows
If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
Selection.EntireRow.Delete
End If
Next Rw
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
MsgBox "Blank Rows Removed"
End Sub

 

Click on the Visual Basic under the Developer tab in Excel. Insert the code in the appeared blank box and run it. You will see a box saying Blank Rows Removed appears. Finally, press OK to continue.

Removing Blank Rows using Excel VBA

Note: You have to bear that in mind that this code doesn’t work on a table. So, if you want to delete the blank rows in a table, you can not do that with this code. For running this code, you may need to enable excel macro.

Conclusion

As we can see there are so many ways of deleting blank rows in excel ranging from simple to complex. Choose the method that is best for you. You need be very watchful while deleting the blank rows as it may also delete other cells of your spreadsheets.

Please do share any other techniques that you know will be helpful.


Siam Hasan Khan on FacebookSiam Hasan Khan on Linkedin
Hello!

Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and attitude to grow continuously. Continuous improvement and life-long learning is my motto.

We will be happy to hear your thoughts

      Leave a reply