Fix Excel Filter Stopping at Blank Row – 4 Quick Solutions

We applied a filter to a dataset that has a blank row, but it stopped filtering when it encountered the blank row.

Fix 1 – Selecting the Whole Range

Steps:

• Select all the cells from B4 to C10 and go to the Data tab on top of the screen.
• Click on Filter under Sort & Filter.

• You should get the filter option in the dataset including all the data rows.

Fix 2 – Using the COUNTBLANK Function

Steps:

• Insert the following formula in cell D5:

• Press Enter and copy this formula down using the Fill Handle.

• Select all the cells from B4 to D10 and click on Filter under Sort & Filter.

• This will add the filter option to this dataset.

• Click on No. of Blank Cells drop-down and select only the 0 box from the filter options.

• This will remove all the blank cells from the filter.

Fix 3 – Sorting the Dataset

Steps:

• Select all the cells from B4 to C10 and select A-Z sorting under Sort & Filter.

• This will separate the blank cells at the end of the dataset and then you can apply filtering to it.

Read More: How to Filter Multiple Rows in Excel

Fix 4 – Applying VBA Code

Steps:

• Go to the Developer tab and select Visual Basic.

• Select Insert in the VBA window and click on Module.

• Insert the following code in the module:

Public Sub FilterBlank()
Worksheets("VBA").Range("B4:C10").AutoFilter
End Sub

• Go to the Developer tab and click Macros.

• In the Macro window, select the FilterBlank macro and click Run.

• The VBA code will apply the necessary filtering.

How to Fix Excel Filter Is Not Working with Merged Cells

Steps:

• Select the merged cells and click on the Merge & Center icon as in the image below.
• This should solve the problem and you can now select the data cells to apply the filter.

Read More: Excel Not Filtering Entire Column

How to Exclude Blank Cells in the Advanced Filter in Excel

Steps:

• Go to cell E6 and insert the following formula:

• Press the Enter key from the keyboard.
• Select the cells from B4 to C10 and click on Advanced under Sort & Filter.

• Fill up the List range and Criteria range as below and click OK.

• This will remove any blank cells present in the dataset.

Download the Practice Workbook

<< Go Back to Filter in Excel | Learn Excel