How to Delete Blank Rows in Excel – Remove Empty Rows – 3 Easy Tips

1. Excel Delete Multiple Blank Rows (Built in Option)

To remove blank rows in Excel, Open the worksheet & follow these menu options.

1. Select data range that has empty Rows.
2. Click on option Home -> Find & Select -> choose “Special”.
3. Select ‘Blanks’ & click OK in the Pop-up menu.
4. Empty Cells in the selected Range will be highlighted.
5. Click Home menu -> Delete -> Delete Sheet Rows.

Now, notice that this option will delete all empty rows in Excel & the user can happily move on to processing only the valid data.

Lets see other ways on how to identify & remove empty rows in between huge data rows.

Also Read: Remove Duplicate Values From Excel Sheet

2. Excel VBA Delete Empty Rows or Highlight

This Excel VBA code will delete all empty rows in the sheet1 one by one.

Modify the code to include the sheet name if you want to perform this operation on a different worksheet.

Note: Below Excel Macro is limited to 256 columns. If your Excel version has more number of columns, then change the data type of variables from Integer to Double and increase the column limits from 256.

Option Explicit
'Code from Officetricks.com - How to Delete Blank Rows in Excel using VBA?
Sub Excel_VBA_Delete_Blank_Rows()
    Dim iRow As Double, iCol As Double
    iRow = 1
    iCol = 1
    
    While True
        'Check whether Cell 1 is Blank for that a row
        ActiveSheet.Cells(iRow, iCol).Select
        If VBA.Trim(ActiveSheet.Cells(iRow, iCol)) = "" Then
        
            While True
                'If Cell 1 is Blank then verify all the columns in that row
                If VBA.Trim(ActiveSheet.Cells(iRow, iCol)) <> "" Then
                    GoTo Skip_To_Next_Row:
                End If
                iCol = iCol + 1
                If iCol > 256 Then GoTo Del_Row:
            Wend
Del_Row:
            'If first cell is Empty, then execute VBA Code to Delete a Row in Excel
            ActiveSheet.Rows(iRow).Delete Shift:=xlUp
            iRow = iRow - 1
        End If
        
Skip_To_Next_Row:
        iRow = iRow + 1
        If ActiveSheet.UsedRange.Rows.Count < 2 Then GoTo End_Process:
        If iRow > ActiveSheet.UsedRange.Rows.Count Then GoTo End_Process:
        iCol = 1
    Wend
    
End_Process:
    MsgBox "Excel Delete Blank Rows Completed - Thanks for visiting Officetricks.com"
End Sub

The above Excel VBA code, scans picks each column in each row & deletes that row only if complete rows has no data.

If you are facing any issues while deleting empty rows using this VBA code, then leave us a comment.

3. Remove Empty Rows Add-in for Excel

If you have lot of Excel files in which the empty rows has to be removed, then use our free Add-in.

1. Download the below add-in file to this location: C:\Users\<ActiveUserName>\AppData\Roaming\Microsoft\AddIns
2. Open the Excel workbook that has lot of blank rows.
3. Click on Office Ribbon on left hand menu top.
4. Select “Excel Options” -> “Add-Ins”.
5. Choose “Excel Add-Ins” in Manage field and click “Go” button.
6. Click on check box for ‘Delete Blank Rows” & click ok.

Now, go to your Excel workbook and press Ctrl + d. Then this add-in function will be invoked and all blank rows will be deleted.

Download Excel VBA Delete Empty Rows Add-In Downloaded 381 Times

In case if you want more customized process to be followed, then use the code in this page.

Sample Excel VBA to Delete Empty rows Downloaded 561 Times

More Tips: Remove Junk Data From Text File

Excel Remove Blank Rows with Built in Option

In the introduction, we just learned how to use the built-in option available in Excel to remove empty rows in Excel, What is the limitation in using the Built in option? and How to overcome the limitation using a VBA to delete blank rows?.

Well, if you are in hurry you can directly download sample excel document or use the VBA at end of this topic to remove empty rows.

Limitation with Built in Delete Empty Rows

Consider that data range or table selected does not have empty rows. Instead, only some of the columns or cells are empty.

In this case, if you choose the built-in option, it will delete row completely even if any single cell has no data. This will result in data loss.

Example:

• Row/Column ColumnA ColumnB ColumnC
• Row1 DataA1 DataB1 DataC1
• Row2 DataA2 <Empty> DataC2
• Row3 DataA3 DataB3 DataC3

In this example, Row2 has an empty cell. If you choose the steps explained in Build in Option, then it will delete Row2 also. So, you data in Row2 will be lost or it will delete row2 column 2. and DataB3 will be moved up.

This will result in mismatched data in the table. So, it is always better to go with the Excel VBA code to remove blank rows.

Microsoft keep on adding new features in Excel, one of the most widely used Office Applications. In Excel, we might be having lot of Empty rows, cells, columns while processing data. It creates problems for us in getting desired result.

There is another interesting Plugins from AbleBits website for such quick Excel operations.