Delete Blank Rows in Excel

How to Delete Blank Rows in Excel 2019/2016/2013/2010

Blank rows in an Excel spreadsheet are of no use and the only thing they do is create clutter in your file. The best thing to do with them is to get them removed from your spreadsheet. The question is how to delete blank rows in Excel? Well, there are multiple ways to delete blank rows in an Excel file on your computer and the following guides cover many of them.

These methods allow you to get rid of any rows that do not contain any information. What that means is these methods remove the rows that are completely blank and have content in them. All of these methods are easy to use and they get the job done in a few seconds on your computer.

Also, note that these methods should work in most Excel versions including 2019, 2016, 2013, and 2010.

Way 1. Delete Blank Rows in Excel Using Go To Special Command

One of the ways to Excel remove empty rows is to use the Go To Special option in the software. It allows you to get to the rows that are blank and then offers you the option to remove them from your spreadsheet. It only requires a few clicks and you have your rows removed from your file.

You should keep in mind, though, that this removes the rows where there is even one cell with no data on it. What that means is if you have a row where there is one cell with data and another with no data in it, it will remove both the cells from your spreadsheet. Be cautious while performing this method.

Step 1. Launch your spreadsheet in Microsoft Excel on your computer.

Step 2. When the spreadsheet opens, click on the Home tab at the top if you are not already there. Then, find the section that says Editing, click on the Find & Select option, and choose Go To Special.

Step 3. From the Select section, choose the option that says Blanks. Then, click on the OK button.

Step 4. All the blank rows in your spreadsheet should now be selected. To delete them, click on the Home tab at the top, select the Delete option, and then choose Delete Sheet Rows.

Your blank rows will be deleted.

Way 2. Delete Excel Blank Rows Using Excel Filter

Another way to get rid of blank rows from your spreadsheet is to use an Excel filter.

Step 1. Open your spreadsheet in Excel and select the entire table.

Step 2. Click on the Data tab at the top and then select the option that says Filter.

Step 3. Click on the down-arrow icon for the first column, untick the Select All option, checkmark the Blanks option, and then click on OK.

Step 4. Press Ctrl + Home and then press Ctrl + Shift + End. Right-click on any cell and select Delete Row.

It will delete your blank rows.

Way 3: Delete Blank Rows in Excel with VBA Code

If you do not want to manually select any options to remove blank rows, you can use a VBA code that does the entire task automatically for you.

Step 1. Open your spreadsheet in Excel and press the Alt + F11 keys at the same time. It will open Visual Basic Editor.

Step 2. Right-click on your spreadsheet in the left sidebar, select Insert, and then choose the option that says Module. It will help you add a VBA module to your file.

Step 3. Copy the following code and paste it into the VBA code editor.

Sub remove_empty_lines() Dim start_time, arr(), rng, row_num As Long, column_num As Long, i, j
Application.ScreenUpdating = False
row_num = ActiveSheet.UsedRange.Rows.Count
column_num = ActiveSheet.UsedRange.Columns.Count
ReDim arr(1 To row_num)
rng = ActiveSheet.UsedRange
start_time = Timer
For i = 1 To row_num
For j = 1 To column_num
If rng(i, j) <> "" Then arr(i) = i
Next j, i
With Cells(ActiveSheet.UsedRange.Row, ActiveSheet.UsedRange.Column + column_num).Resize(row_num, 1)
.Value = WorksheetFunction.Transpose(arr)
ActiveSheet.UsedRange.Sort Key1:=Cells(ActiveSheet.UsedRange.Row, ActiveSheet.UsedRange.Column + column_num)
.Value = ""
End With
Application.ScreenUpdating = True
MsgBox Format(Timer - start_time, "0.00s")
End Sub

Step 4. Save the code and run it.

The blank rows should be deleted from your Excel spreadsheet.

Bonus Tips: The Best Excel Password Recovery Software for Word 2019/2016/2013/2010

If you are trying to open a password protected Excel file, you will notice that it does not open without entering the correct password. If you cannot recall the password, your best bet is to use a password recovery software.

Excel Password Recovery is a great software to recover the passwords for your locked Excel files. It helps you easily gain access to your encrypted Excel files without knowing the password.

• Instantly Recover passwords for your Excel files
• Multiple attack types to recover Excel password efficiently.
• GPU acceleration
• Supports Excel 2019/2016/2013/2010/2007/2003.
• Support for multilingual Excel files

If you wish to use it, the following shows how to do it.

Download SmartKey Excel Password Recovery:

• Download and launch the software. Click on Add to add your Excel file.
  
  
• Select an attack type and click on Start.
  
  
• After selecting an attack type, click on the button that says Start to start unlocking your Excel file.
  
  
• You will see the recovered password on your screen.
  
  

Use the recovered password to access your protected Excel file.

If you want to unclutter your Excel files, removing blank rows is the first step. Our guide above on how to delete blank rows in Excel should help you take that first step towards making your spreadsheets clean and neat. Excel Password Recovery is highly recommended if your are looking for a professional Excel password recovery program.

Download SmartKey Excel Password Recovery:

comments powered by Disqus