How to Skip Blank Rows Using a Formula in Excel – 8 Methods

The sample dataset contains blank rows.

Method 1 – Using Keyboard Shortcuts to Skip Blank Rows in Excel

Step 1:

• Select B5:F14, and press F5.

• In the Go To window, enter the cell reference B5:F14 in Reference.
• Click Special.

• In the Go To Special dialog box, select Blank in Select.
• Click OK.

Step 2:

You can see the blank rows.

• Press Ctrl + minus(-).
• In the Delete window, select Entire row in Delete.
• Click OK.

This is the output.

Read More: How to Skip Cells in Excel Formula

Method 2 – Using the Delete Command to Skip Blank Rows in Excel

Step 1:

• Select B4:F14.
• In the Data tab, go to:

Data → Sort & Filter → Filter

• The Filter will be enabled in the column headers.
• Click the Filter sign and check Blank.
• Click OK.

Step 2:

• In the Home tab, go to:

Home → Cells → Delete → Delete Sheet Rows

• Click Delete Sheet Rows.

• To remove the Filter sign from the header, go to:

Data → Sort & Filter → Filter

This is the output.

Read More: How to Skip Lines in Excel

Method 3 – Applying the COUNTBLANK Function to Skip Blank Rows in Excel

Step 1:

• Enter the following formula in G5.

• Press ENTER.

0 is returned by the COUNTBLANK function.

• Drag down the Fill Handle to see the result in the rest of the cells.

Step 2:

You can see the count of blank cells.

• Select the whole data table.
• Press CTRL + SHIFT + L to enable the Filter feature.

• Click the drop-down icon in the Blank column.
• Select 0 and click OK.

This is the output.

Read More: How to Skip Columns in Excel Formula

Method 4 – Using the FILTER Function to Skip Blank Rows in Excel

Steps:

• Enter the following formula in G5.

• Press ENTER.

This is the output.

Read More: Excel Formula to Skip Rows Based on Value

Method 5 – Merge the IF, AND & ISBLANK Functions to Skip Blank Rows in Excel

Step 1:

• Enter the following formula in F5.

• Press ENTER.

You will see Not-Blank as the output.

• Drag down the Fill Handle to see the result in the rest of the cells.

Step 2:

• Select the whole data table and press CTRL + SHIFT + L to apply the Filter feature.
• Click the drop-down icon in the Blanks column header.
• Uncheck Blank.
• Click OK.

• Press ENTER.

This is the output.

Read More: How to Skip a Column When Selecting in Excel

Method 6 – Combine the IFERROR, INDEX, SMALL, IF, ROW, & ROWS Functions to Skip Blank Rows

Step 1:

• Enter the following formula in G5.

• Press ENTER.

Smith is the output.

Step 2:

• Drag the Fill Handle icon to J5.

• Drag it again to cell J11.
• All rows with blank cells are excluded.

Method 7 – Merge the IF, ROWS, COUNT, INDEX, SMALL, ROW, and ROWS Functions to Skip Blank Rows in Excel

Steps:

• Enter the following formula in G5.

• Press ENTER.

Smith is the output.

• Drag the Fill Handle to column J.

• Drag it again to the end of the CE. 

This is the output.

Method 8 – Run a VBA Code to Skip Blank Rows in Excel

Step 1:

• In the Developer tab, go to:

Developer → Visual Basic

• Select Visual Basic.
• The Microsoft Visual Basic for Applications – Skip Blank Rows will be displayed.
• Go to:

Insert → Module

Step 2:

• In the Skip Blank Rows module, enter the VBA code.

Sub Skip_Blank_Rows()
Range("B5:F14").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

• To run the VBA:

Run → Run Sub/UserForm

Step 3:

• Go back to the active worksheet.

This is the output.

Things to Remember

#NAME occurs when the range name is incorrect.

#REF! occurs when a cell reference is not valid.

The FILTER function is available in Excel 365 only.

Download Practice Workbook

Download the practice workbook.

Related Articles

• How to Skip Cells When Dragging in Excel
• How to Skip Hidden Cells When Pasting in Excel
• Skip to Next Result with VLOOKUP If Blank Cell Is Present
• How to Skip to Next Cell If a Cell Is Blank in Excel

<< Go Back to Excel Cells | Learn Excel