[Fixed!] Excel Filter Stops at Blank Row (4 Possible Solutions)

In this tutorial, I am going to show you 4 possible solutions to Excel filter stops at blank rows. You can use these fixes in your own dataset to find blank cells and include them in the Filter. Throughout this tutorial, you will also learn some important Excel tools and techniques that will be very useful in any Excel-related task.

Excel Filter Stops at Blank Row: 4 Possible Solutions

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 7 rows and 2 columns. Initially, we are keeping all the cells in Accounting format. For all the datasets, we have 2 unique columns which are Item and Price. Although we may vary the number of columns later on if that is needed.

1. Selecting Whole Range

The first solution we can try is to select the range of the dataset before applying the filter option. Follow the steps below to do this.

Steps:

• First, select all the cells from B4 to C10 and go to the Data tab on top of the screen.
• Now, click on Filter under Sort & Filter.

• As a result, you should get the filter option in the dataset including all the data rows.

2. Using COUNTBLANK Function

The COUNTBLANK function in Excel can count the number of blank cells in a range of data that we specify as input. Let us see how to use this function to solve the Excel filter stops at blank row problem.

Steps:

• To begin with, insert the following formula in cell D5:

• Next, press Enter and copy this formula down using Fill Handle.

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

• Immediately, this will add the filter option to this dataset.

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

• Consequently, this will remove all the blank cells from the filter.

3. Sorting Dataset

Another fix that we can try is to first sort out the dataset and then apply the filter option. Let us see how to do this in detail.

Steps:

• To start with this method, select all the cells from B4 to C10 and select A-Z sorting under Sort & Filter.

• As a result, 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

4. Applying VBA Code

If you are familiar with VBA in excel, then you can solve this problem on hand just by writing a few lines of code. Below are the steps to do this.

Steps:

• For this method, go to the Developer tab and select Visual Basic.

• Now, select Insert in the VBA window and click on Module.

• Next, type in the formula below in the new window:

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

• Then, open the macro from the Developer tab by clicking on Macros.

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

• As a result, the VBA code will apply the necessary filtering including all the data rows.

How to Fix When Excel Filter Is Not Working

In case your filtering option in Excel is not working, then you can try the following steps to quickly fix that.

Steps:

• For this, select the merged cells and click on the Merge & Center icon as in the image below.
• Finally, 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 Using Advanced Filter in Excel

The existence of unnecessary blank cells within our dataset can cause some problems at times. So it is better to remove any blank cells that are not important. We will see how to do this in the following steps.

Steps:

• To start with, go to cell E6 and type in the following formula:

• Now press the Enter key from the keyboard.
• Next, select the cells from B4 to C10 and click on Advanced under Sort & Filter.

• Here, fill up the List range and Criteria range as below and click OK.

• Finally, this will remove any blank cells present in the dataset.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

I hope that you were able to understand the methods that I showed in this tutorial on 4 possible solutions to Excel Filter stops at blank row problem. As you can see, there are quite a few ways to solve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. If you have any queries, please let me know in the comments.

<< Go Back to Not Working | Filter in Excel | Learn Excel